如何在BigQuery中动态提取JSON数组生成公司ID列？

输入JSON示例 #

{
    "type": "list",
    "data": [
        {
            "id": "5bc7a3396fbc71aaa1f744e3",
            "type": "company",
            "url": "/companies/5bc7a3396fbc71aaa1f744e3"
        },
        {
            "id": "5b0aa0ac6e378450e980f89a",
            "type": "company",
            "url": "/companies/5b0aa0ac6e378450e980f89a"
        }
    ],
    "url": "/contacts/5802b14755309dc4d75d184d/companies",
    "total_count": 2,
    "has_more": false
}

目标输出 #

实现方案 #

由于需要根据数组长度动态生成列，需结合UNNEST（带偏移量）和EXECUTE IMMEDIATE动态SQL实现：

场景1：JSON存储在表字段中

假设表名为your_table，JSON字段名为json_data，执行以下SQL：

DECLARE cols STRING;

-- 生成动态列的聚合语句
SET cols = (
  SELECT STRING_AGG(DISTINCT CONCAT('MAX(IF(offset = ', offset, ', id, NULL)) AS company_', offset), ', ')
  FROM your_table,
  UNNEST(JSON_EXTRACT_ARRAY(json_data, '$.data')) WITH OFFSET AS offset
);

-- 动态执行透视查询
EXECUTE IMMEDIATE CONCAT('
  SELECT ', cols, '
  FROM (
    SELECT 
      JSON_VALUE(item, ''$.id'') AS id,
      offset
    FROM your_table,
    UNNEST(JSON_EXTRACT_ARRAY(json_data, ''$.data'')) WITH OFFSET AS offset,
    UNNEST([item]) AS item
  )
  GROUP BY TRUE
');

场景2：直接使用JSON字符串

如果是单条JSON字符串，可直接传入处理：

DECLARE json_str STRING DEFAULT '''{
    "type": "list",
    "data": [
        {
            "id": "5bc7a3396fbc71aaa1f744e3",
            "type": "company",
            "url": "/companies/5bc7a3396fbc71aaa1f744e3"
        },
        {
            "id": "5b0aa0ac6e378450e980f89a",
            "type": "company",
            "url": "/companies/5b0aa0ac6e378450e980f89a"
        }
    ],
    "url": "/contacts/5802b14755309dc4d75d184d/companies",
    "total_count": 2,
    "has_more": false
}''';

DECLARE cols STRING;

SET cols = (
  SELECT STRING_AGG(DISTINCT CONCAT('MAX(IF(offset = ', offset, ', id, NULL)) AS company_', offset), ', ')
  FROM UNNEST(JSON_EXTRACT_ARRAY(json_str, '$.data')) WITH OFFSET AS offset
);

EXECUTE IMMEDIATE CONCAT('
  SELECT ', cols, '
  FROM (
    SELECT 
      JSON_VALUE(item, ''$.id'') AS id,
      offset
    FROM UNNEST(JSON_EXTRACT_ARRAY(json_str, ''$.data'')) WITH OFFSET AS offset,
    UNNEST([item]) AS item
  )
  GROUP BY TRUE
');

关键逻辑说明 #

1. JSON_EXTRACT_ARRAY提取JSON中的data数组，UNNEST ... WITH OFFSET为每个数组元素添加索引（0、1...）。
2. JSON_VALUE从每个数组元素中取出公司ID。
3. 通过STRING_AGG拼接出动态列的聚合表达式，再用EXECUTE IMMEDIATE执行动态生成的透视SQL，实现按索引生成对应列。

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