BigQuery SQL中如何解析字符串格式JSON字段提取目标键值？

你之前写的SQL无法正常运行，核心是两个用法错误：

• unnest函数仅用于将数组/列表类型的字段拆分为多行数据，你的data字段是单条JSON格式的对象字符串，不是数组结构，完全不适用这个函数，强行调用只会触发类型不匹配报错。
• 字符串类型存储的JSON内容无法直接被结构化读取，必须通过对应数据库内置的JSON解析函数，才能提取到内部的指定字段。

根据你使用的数据库引擎，选择对应SQL即可，查询结果会直接返回每条记录的id和对应的userId值：

• BigQuery

SELECT
  id,
  JSON_VALUE(data, '$.userId') AS user_id
FROM your_table

如果遇到JSON转义导致的解析异常，可以替换为JSON_EXTRACT_SCALAR(data, '$.userId')，效果完全一致。

• PostgreSQL

SELECT
  id,
  (data::JSONB ->> 'userId')::INTEGER AS user_id
FROM your_table

逻辑是先把字符串类型的data转为可操作的JSONB对象，提取出字符串格式的userId后，再转为整数类型匹配样例里的数字格式。

• MySQL 5.7及以上版本

SELECT
  id,
  data->>'$.userId' AS user_id
FROM your_table

->>是MySQL内置的JSON快捷操作符，等价于JSON_UNQUOTE(JSON_EXTRACT(data, '$.userId'))，直接返回提取到的userId值。

• Hive/Spark SQL

SELECT
  id,
  get_json_object(data, '$.userId') AS user_id
FROM your_table

如果运行时出现JSON解析类报错，先排查data字段是否存在格式破损、多余转义、混入非JSON内容的脏数据，清洗完成后再执行解析即可。

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