BigQuery：将重复字符串字段内的JSON数据转置为表格

实现思路 #

先把重复的字符串字段展开为单行记录，再逐个解析每个JSON字符串里的字段，最终生成目标表格。

示例SQL（以BigQuery为例） #

WITH raw_data AS (
  -- 模拟原始数据
  SELECT ["{\"item_id\":\"186\",\"item_name\":\"abc\"}","{\"item_id\":\"470\",\"item_name\":\"efg\"}"] AS cart_items
)
SELECT
  -- 解析JSON字符串中的字段
  JSON_VALUE(item_json, '$.item_id') AS item_id,
  JSON_VALUE(item_json, '$.item_name') AS item_name
FROM raw_data,
  -- 展开重复字段为单行
  UNNEST(cart_items) AS item_json

最终输出表格 #

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