BigQuery中如何解析字符串内键值对动态生成对应列

核心实现分两个阶段：先将字符串存储的JSON问答数组拆分为逐行的单条问答记录，再通过条件聚合将问题行透视为独立列，无匹配答案的位置自动返回空值。

具体实现步骤 #

• 解析拆分阶段：将字符串类型的response_group字段转换为JSON数组类型，通过UNNEST把数组中每个包含Question、Answer键的对象拆为独立行，分别提取问题、答案内容
• 透视聚合阶段：按id分组，用条件聚合匹配每个问题对应的答案，直接生成你需要的固定列宽表，不需要额外动态SQL逻辑

可直接运行的参考代码 #

-- 替换下方CTE中的raw_data为你的实际业务表即可
WITH raw_data AS (
  -- 模拟原始表数据，实际使用时删除该段直接引用你的表
  SELECT 'A1' AS id, '[{"Question":"what''s your favourite colour?","Answer":"blue"},{"Question":"do you prefer dogs or cats?","Answer":"dogs"},{"Question":"do you prefer tea or coffee?","Answer":"coffee"}]' AS response_group
  UNION ALL
  SELECT 'A2' AS id, '[{"Question":"what''s your favourite colour?","Answer":"green"},{"Question":"who''s your favourite superhero?","Answer":"Superman"},{"Question":"do you prefer tea or coffee?","Answer":"coffee"}]' AS response_group
),
parsed_qa_pairs AS (
  SELECT
    id,
    JSON_VALUE(qa_item, '$.Question') AS question,
    JSON_VALUE(qa_item, '$.Answer') AS answer
  FROM raw_data,
  UNNEST(JSON_EXTRACT_ARRAY(SAFE.PARSE_JSON(response_group))) AS qa_item
)
SELECT
  id,
  MAX(IF(question = "what's your favourite colour?", answer, NULL)) AS `what's your favourite colour?`,
  MAX(IF(question = "do you prefer dogs or cats?", answer, NULL)) AS `do you prefer dogs or cats?`,
  MAX(IF(question = "do you prefer tea or coffee?", answer, NULL)) AS `do you prefer tea or coffee?`,
  MAX(IF(question = "who's your favourite superhero?", answer, NULL)) AS `who's your favourite superhero?`
FROM parsed_qa_pairs
GROUP BY id
ORDER BY id

注意事项 #

• 代码中用SAFE.PARSE_JSON处理JSON格式异常的脏数据，避免单条格式错误导致整个查询失败，脏数据对应的答案列会自动留空
• 该方案适配固定列场景，后续如果新增问题类型，只需要在最终SELECT块中新增对应的MAX(IF(...))聚合语句即可，无需修改上游解析逻辑
• 不要用正则、字符串截取的方式提取答案，原生JSON解析函数的准确性和查询性能都远高于字符串处理方案
• 运行后返回结果和预期结构完全一致：A1的偏好猫狗列值为dogs、超级英雄列为空；A2的超级英雄列值为Superman、偏好猫狗列为空，其余公共列正常填充对应答案

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