如何在BQ中使用JSON_EXTRACT提取JSON内的动态键及对应值

首先明确：JSON_EXTRACT仅支持提取指定路径下的JSON值，无法直接遍历未知的动态键，需要配合其他JSON函数组合实现需求，以下是MySQL环境下的常用实现方案：

提取z对象所有动态键 #

使用JSON_KEYS可直接获取z下所有键组成的JSON数组：

SELECT JSON_KEYS(存储JSON的字段名, '$.z') AS z_dynamic_keys FROM 你的表名;

示例输出：["12345", "56789", "23456"]

提取动态键+对应取值（MySQL 8.0+） #

8.0及以上版本支持JSON_TABLE，可以直接把数组拆成多行，再配合JSON_EXTRACT取值：

SELECT 
  temp.z_key,
  JSON_UNQUOTE(JSON_EXTRACT(你的表名.存储JSON的字段名, CONCAT('$.z."', temp.z_key, '"'))) AS z_value
FROM 你的表名,
JSON_TABLE(
  JSON_KEYS(存储JSON的字段名, '$.z'),
  '$[*]' COLUMNS (z_key VARCHAR(255) PATH '$')
) AS temp;

该写法会自动适配z下0-5个键的所有场景，z为空对象时无返回结果，符合预期。示例输出如下：

提取动态键+对应取值（MySQL 5.7 低版本） #

低版本不支持JSON_TABLE，可以用辅助数字索引表适配最多5个键的场景：

SELECT
  JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(存储JSON的字段名, '$.z'), CONCAT('$[', n.idx, ']'))) AS z_key,
  JSON_UNQUOTE(JSON_EXTRACT(存储JSON的字段名, CONCAT('$.z."', JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(存储JSON的字段名, '$.z'), CONCAT('$[', n.idx, ']'))), '"'))) AS z_value
FROM 你的表名
-- 覆盖0-4共5个索引，对应最多5个键的需求
JOIN (SELECT 0 AS idx UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) n
WHERE JSON_EXTRACT(JSON_KEYS(存储JSON的字段名, '$.z'), CONCAT('$[', n.idx, ']')) IS NOT NULL;

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