在BigQuery中基于多字符串拆分JSON为多条记录的方案咨询

问题背景 #

现有如下格式的输入数据：

id| data                                                                              
------------------------------------------------------------------------------------------------------------------------
1 | {"desc": "ABC", "sno": 22, "maincode": 3345},{"desc": "XYZ", "sno": 41, "maincode": 3100}
2 | {"value": 3340,"desc": "ABC", "sno": 22, "maincode": 3345},{"value": 400,"desc": "XYZ", "sno": 22, "maincode": 2205}

每条记录的data字段包含多个独立JSON对象，以逗号分隔。需要将这些JSON拆分为对应同一id的多行记录，预期输出如下：

id| data                                                                              
-------------------------------------------------------------------
1 | {"desc": "ABC", "sno": 22, "maincode": 3345}
1 | {"desc": "XYZ", "sno": 41, "maincode": 3100}
2 | {"value": 3340,"desc": "ABC", "sno": 22, "maincode": 3345}
2 | {"value": 400,"desc": "XYZ", "sno": 22, "maincode": 2205}

需要适配JSON对象以不同键开头的情况（已知完整键列表），避免直接用},{拆分后拼接符号的繁琐操作。

解决方案 #

方法1：正则表达式精准匹配（适配已知键列表） #

既然已经掌握所有可能的起始键（如desc、value等），可以构造正则直接提取每个完整的JSON对象，无需手动拼接符号：

• 匹配规则：({"(?:desc|value)":.*?})，其中(?:desc|value)替换为实际的所有键列表，用|分隔
• 核心逻辑：遍历每条输入记录，用正则提取所有符合规则的JSON对象，再给每个对象绑定对应的id

以Python为例，代码实现如下：

import re

# 输入数据（跳过表头和分隔线）
input_lines = [
    "1 | {\"desc\": \"ABC\", \"sno\": 22, \"maincode\": 3345},{\"desc\": \"XYZ\", \"sno\": 41, \"maincode\": 3100}",
    "2 | {\"value\": 3340,\"desc\": \"ABC\", \"sno\": 22, \"maincode\": 3345},{\"value\": 400,\"desc\": \"XYZ\", \"sno\": 22, \"maincode\": 2205}"
]

# 替换为你实际的所有起始键
keys = "desc|value"
pattern = re.compile(r'({"(?:' + keys + r')":.*?})')

# 输出结果
print("id| data")
print("-------------------------------------------------------------------")
for line in input_lines:
    id_part, data_part = line.split(" | ", 1)
    json_objs = pattern.findall(data_part)
    for obj in json_objs:
        print(f"{id_part} | {obj}")

方法2：JSON数组解析（更严谨，适配复杂场景） #

如果JSON对象内部可能包含逗号（比如字符串值里的逗号），正则会失效。此时可以把data字段内容包裹成合法JSON数组，用标准JSON解析器处理，完全避免正则的局限性：

Python代码示例：

import json

input_lines = [
    "1 | {\"desc\": \"ABC\", \"sno\": 22, \"maincode\": 3345},{\"desc\": \"XYZ\", \"sno\": 41, \"maincode\": 3100}",
    "2 | {\"value\": 3340,\"desc\": \"ABC\", \"sno\": 22, \"maincode\": 3345},{\"value\": 400,\"desc\": \"XYZ\", \"sno\": 22, \"maincode\": 2205}"
]

# 输出结果
print("id| data")
print("-------------------------------------------------------------------")
for line in input_lines:
    id_part, data_part = line.split(" | ", 1)
    # 转为合法JSON数组
    json_array_str = f"[{data_part}]"
    try:
        json_objs = json.loads(json_array_str)
        for obj in json_objs:
            # 转回格式化的JSON字符串
            obj_str = json.dumps(obj)
            print(f"{id_part} | {obj_str}")
    except json.JSONDecodeError as e:
        print(f"解析错误：{e}")

内容的提问来源于stack exchange，提问作者Sri Bharath