如何将字符串字段中的嵌套键值对拆分提取为独立数据列

你的需求是解析JSON格式的字符串字段，提取顶层键值对为独立列，不需要使用UNNEST函数（UNNEST适用于将数组元素拆分为独立行的场景，和当前需求不匹配），使用SQL内置的JSON解析函数即可实现，不同SQL引擎的实现方式如下：

注意：先确认你的attributes字段是合法JSON格式，你给出的样例中US/EU没有加双引号，属于非法JSON格式，生产环境如果是这种情况需要先做字符串清洗，补全引号后再解析。

1. Hive/Spark SQL 环境 #

推荐使用json_tuple函数，一次可提取多个键，效率高于逐键调用get_json_object：

SELECT 
  ID,
  json_tuple(attributes, 'category', 'region', 'item_id') AS (category, region, item_id)
FROM 你的表名;

2. BigQuery 环境 #

使用JSON_VALUE提取标量值，JSON_EXTRACT保留数组原始格式：

SELECT
  ID,
  JSON_VALUE(attributes, '$.category') AS category,
  JSON_VALUE(attributes, '$.region') AS region,
  JSON_EXTRACT(attributes, '$.item_id') AS item_id
FROM 你的表路径;

3. MySQL 8.0+ 环境 #

使用->>操作符提取值并自动去除外层引号，->操作符保留数组原始格式：

SELECT
  ID,
  attributes->>'$.category' AS category,
  attributes->>'$.region' AS region,
  attributes->'$.item_id' AS item_id
FROM 你的表名;

动态列扩展说明 #

如果attributes字段的键不固定，需要自动提取所有键作为列，需要编写动态SQL实现：先查询出所有去重的键名，再拼接上述查询逻辑即可。

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