如何在BigQuery中将含随机键的JSON字段转换为数组？

可以通过BigQuery的JSON函数组合实现需求，核心思路是先提取所有随机键，再将每个键与其对应的值合并为新对象，最后聚合为数组。

通用解决方案（适配值内任意字段） #

如果cart里的子对象字段不固定，用JSON_MERGE_PATCH直接合并键和对应值，无需指定具体字段：

SELECT
  uid,
  ARRAY_AGG(
    JSON_MERGE_PATCH(
      JSON_OBJECT('key', `key`),
      JSON_EXTRACT(cart, CONCAT('$.', `key`))
    )
  ) AS cart
FROM
  `your-project.your-dataset.your-table`,
  UNNEST(JSON_EXTRACT_KEYS(cart)) AS `key`
GROUP BY
  uid

指定字段的解决方案（如果子对象字段固定） #

如果子对象的字段是确定的（比如只有field1和field2），也可以显式提取字段组合：

SELECT
  uid,
  ARRAY_AGG(
    JSON_OBJECT(
      'key', `key`,
      'field1', JSON_VALUE(json_val, '$.field1'),
      'field2', JSON_VALUE(json_val, '$.field2')
    )
  ) AS cart
FROM (
  SELECT
    uid,
    `key`,
    JSON_EXTRACT(cart, CONCAT('$.', `key`)) AS json_val
  FROM
    `your-project.your-dataset.your-table`,
    UNNEST(JSON_EXTRACT_KEYS(cart)) AS `key`
)
GROUP BY
  uid

关键函数说明 #

• JSON_EXTRACT_KEYS(cart)：提取JSON对象的所有顶层键，返回字符串数组
• UNNEST(...)：将键数组展开为多行，每行对应一个随机键
• JSON_MERGE_PATCH(obj1, obj2)：合并两个JSON对象，把键对象和对应的值对象合并成新的结构
• ARRAY_AGG(...)：将同一uid下的所有合并对象聚合为数组

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