如何在Snowflake中使用SQL将JSON扁平化拆分为键值两列？

不同数据库的JSON处理函数存在方言差异，以下是主流数据库的可直接运行的实现方案，适配你当前table_1表、单行Value列存储JSON对象的场景，125个键的量级无性能压力。

PostgreSQL #

使用内置的json_each_text（JSONB类型对应jsonb_each_text）函数直接展开JSON对象为键值对行：

SELECT
  j.key AS json_key,
  j.value AS json_value
FROM table_1,
LATERAL json_each_text(table_1.Value::json) j;

• 如果Value列本身已经是json/jsonb类型，可去掉::json类型转换；用jsonb_each_text处理JSONB类型数据性能更优。

MySQL 8.0+ #

利用JSON_KEYS提取所有键名数组，和同顺序提取的值数组通过序数关联匹配：

SELECT
  j.k AS json_key,
  j2.v AS json_value
FROM table_1 t,
JSON_TABLE(
  JSON_KEYS(t.Value),
  '$[*]' COLUMNS (
    k VARCHAR(100) PATH '$',
    ord FOR ORDINALITY
  )
) j
JOIN JSON_TABLE(
  t.Value,
  '$.*' COLUMNS (
    v VARCHAR(100) PATH '$',
    ord FOR ORDINALITY
  )
) j2 ON j.ord = j2.ord;

• 该写法要求MySQL版本为8.0及以上，低版本MySQL没有内置JSON表函数，无法通过原生SQL简洁实现该需求。

SQL Server #

使用OPENJSON函数直接解析JSON对象输出键、值、类型三列，筛选需要的两列即可：

SELECT
  j.[key] AS json_key,
  j.[value] AS json_value
FROM table_1
CROSS APPLY OPENJSON(table_1.Value) j;

SQLite 3.38.0+ #

使用json_tree递归遍历JSON结构，过滤根节点后得到所有叶子键值对：

SELECT
  j.key AS json_key,
  j.value AS json_value
FROM table_1,
json_tree(table_1.Value) j
WHERE j.parent IS NOT NULL
AND j.type = 'string';

通用注意事项：如果Value列存储的JSON字符串存在多余转义，需要先做转义处理再传入JSON解析函数，否则会触发解析报错。

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