BigQuery SQL：如何将数组的数组转换为多列？

问题背景 #

原始数据中Inputs列是JSON结构，包含嵌套的answers数组，每个元素对应不同type及嵌套的answer数组。需要将不同type的answer值提取为独立列，其中LOCATION的多个值需拼接为逗号分隔的字符串。

原始数据结构 #

{
  "answers": [{
    "type": "END_TIME",
    "answer":   [{
      "int_value": 1015
    }]
  },{
    "type": "LOCATION",
    "answer":   [{
      "string_value": "SAN_JOSE"
    },{
      "string_value": "CA"
    }]
  }],
  "username": "xxxxx",
  "status": "COMPLETE"
}

期望输出 #

end_time location       username  status
1015     SAN_JOSE, CA   xxxxx     COMPLETE

当前错误输出 #

end_time                 location                        username   status
[{                       [{                              xxxxx      COMPLETE
      int_value: 1015        string_value: "SAN_JOSE"
    }]                   },{
                             string_value: "CA"
                         }]

当前使用的SQL #

SELECT
  (SELECT answer FROM UNNEST(Inputs.answers) where type = 'END_TIME') end_time,
  (SELECT answer FROM UNNEST(Inputs.answers) where type = 'LOCATION') location,
  t.Inputs.username username,
  t.Inputs.status status
FROM table_name t
;

解决方案 #

问题根源在于直接返回了answer数组对象，未提取嵌套的具体字段。以下是修正后的SQL：

SELECT
  -- 提取END_TIME对应的int_value，用MAX确保取唯一值
  MAX(CASE WHEN a.type = 'END_TIME' THEN (SELECT int_value FROM UNNEST(a.answer)) END) AS end_time,
  -- 拼接LOCATION的所有string_value为逗号分隔字符串
  STRING_AGG(CASE WHEN a.type = 'LOCATION' THEN (SELECT string_value FROM UNNEST(a.answer)) END, ', ') AS location,
  t.Inputs.username AS username,
  t.Inputs.status AS status
FROM table_name t,
  UNNEST(t.Inputs.answers) a
GROUP BY t.Inputs.username, t.Inputs.status;

关键说明 #

1. 提取嵌套字段：通过UNNEST(a.answer)展开每个type对应的嵌套数组，直接提取int_value或string_value。
2. 条件聚合：
   • 对END_TIME使用MAX聚合，确保单个数值被保留。
   • 对LOCATION使用STRING_AGG，将多个string_value拼接为指定格式的字符串。
3. 分组合并：按username和status分组，将同一用户的不同type结果合并为一行。

测试验证（可直接运行） #

WITH table_name AS (
  SELECT JSON_EXTRACT('{
    "answers": [{
      "type": "END_TIME",
      "answer":   [{
        "int_value": 1015
      }]
    },{
      "type": "LOCATION",
      "answer":   [{
        "string_value": "SAN_JOSE"
      },{
        "string_value": "CA"
      }]
    }],
    "username": "xxxxx",
    "status": "COMPLETE"
  }', '$') AS Inputs
)
SELECT
  MAX(CASE WHEN a.type = 'END_TIME' THEN (SELECT int_value FROM UNNEST(a.answer)) END) AS end_time,
  STRING_AGG(CASE WHEN a.type = 'LOCATION' THEN (SELECT string_value FROM UNNEST(a.answer)) END, ', ') AS location,
  JSON_VALUE(Inputs, '$.username') AS username,
  JSON_VALUE(Inputs, '$.status') AS status
FROM table_name t,
  UNNEST(JSON_QUERY_ARRAY(Inputs, '$.answers')) a
GROUP BY JSON_VALUE(Inputs, '$.username'), JSON_VALUE(Inputs, '$.status');

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