如何在Google BigQuery中解析嵌套JSON字符串提取指定字段

问题说明 #

当前表中所有业务数据存储在STRING类型的data字段中，字段值为标准JSON格式字符串，示例如下：

{"id":1381,"email":"J.Smith@gmail.com","name":"Jake Smith","sub_network_ids":[2375,2270],"extended_updated_at":"2022-01-27T00:02:14Z"}

对应表结构参考：

需求为提取JSON中的id、email、name字段生成结构化表，此前组合使用JSON_EXTRACT与UNNEST未得到预期结果。

问题根因 #

提取根节点下的标量值（id、email、name均为单值，非数组）不需要使用UNNEST函数——UNNEST的作用是展开数组类型数据，对标量字段使用反而会导致查询报错或结果异常。此外直接使用JSON_EXTRACT提取字符串类型值时，会返回带JSON格式包裹的结果（比如字符串值会带前后双引号），不符合业务使用要求。

可行SQL写法 #

1. 推荐写法：JSON_EXTRACT_SCALAR #

该函数会直接返回去除JSON格式标记的原生标量值，最适配当前场景：

SELECT
  JSON_EXTRACT_SCALAR(data, '$.id') AS user_id,
  JSON_EXTRACT_SCALAR(data, '$.email') AS user_email,
  JSON_EXTRACT_SCALAR(data, '$.name') AS user_name
FROM `你的项目ID.你的数据集名.你的源表名`

注意事项：

• 路径参数遵循JSONPath语法，$代表JSON根节点，后续拼接要提取的字段名即可，字段名大小写需和JSON内的key完全一致
• 如果存在部分行data为非法JSON格式，可以在函数前加SAFE.前缀（即SAFE.JSON_EXTRACT_SCALAR(...)），非法值会返回NULL，不会中断整个查询
• 如果data字段本身是JSON类型而非STRING类型，上述写法完全兼容，无需额外类型转换

2. 等价写法：JSON_VALUE #

BigQuery标准SQL支持的简化写法，返回结果和JSON_EXTRACT_SCALAR完全一致：

SELECT
  JSON_VALUE(data, '$.id') AS user_id,
  JSON_VALUE(data, '$.email') AS user_email,
  JSON_VALUE(data, '$.name') AS user_name
FROM `你的项目ID.你的数据集名.你的源表名`

附：需要用到UNNEST的场景 #

如果后续需要提取JSON内的数组字段（比如示例中的sub_network_ids，需要把数组内的每个ID拆成单独行），才需要组合JSON_EXTRACT_ARRAY和UNNEST，参考写法如下：

SELECT
  JSON_VALUE(data, '$.id') AS user_id,
  JSON_VALUE(data, '$.email') AS user_email,
  JSON_VALUE(data, '$.name') AS user_name,
  single_network_id
FROM `你的项目ID.你的数据集名.你的源表名`,
UNNEST(JSON_EXTRACT_ARRAY(data, '$.sub_network_ids')) AS single_network_id

常见踩坑汇总 #

• 不要用JSON_EXTRACT直接提取字符串字段：该函数返回的是JSON格式值，提取邮箱会返回带双引号的"J.Smith@gmail.com"，无法直接作为字符串使用
• 不要对标量字段使用UNNEST：仅数组类型字段需要展开，单值字段用UNNEST会触发语法错误或结果笛卡尔积
• JSON路径的字段名严格区分大小写：比如JSON内key为email时，写$.Email会返回NULL

内容的提问来源于stack exchange，提问作者A.Y. Greyson