如何在SQL中提取嵌套JSON列字段值并转换为独立列？

示例数据 #

id    |  col1    |  col2
1      |  Name1 | {'spec_details': {'spec_values': [{'name':'A','value':2}, {'name': 'B', 'value': 5}, {'name': 'C', 'value': 6}], 'spec_id': 'ASVSDAS'}, 'channel': 'channel1'}
2    | Name2 | {'spec_details': {'spec_values': [{'name':'A','value':9}, {'name': 'B', 'value': 1}, {'name': 'D', 'value': 8}], 'spec_id': 'QWSAASS'}, 'channel': 'channel1'}

期望输出 #

id   |  col1  |   A  |  B  |  C  |  D | spec_id
1    | Name1  |   2  |  5  |  6  |    | ASVSDAS
2    | Name2  |   9  |  1  |     |  8 | QWSAASS

实现方案 #

你可以通过数组展开+条件聚合的方式实现需求，具体步骤如下：

1. 展开JSON数组 #

使用jsonb_array_elements（若col2是json类型，改用json_array_elements）将嵌套的spec_values数组拆分为多行，同时保留原表关键字段：

SELECT
  t.id,
  t.col1,
  col2->'spec_details'->>'spec_id' AS spec_id,
  spec_val->>'name' AS spec_name,
  spec_val->>'value' AS spec_value
FROM your_table t,
     jsonb_array_elements(t.col2->'spec_details'->'spec_values') spec_val;

这一步会得到中间结果：

id | col1  | spec_id   | spec_name | spec_value
---|-------|-----------|-----------|-----------
1  | Name1 | ASVSDAS   | A         | 2
1  | Name1 | ASVSDAS   | B         | 5
1  | Name1 | ASVSDAS   | C         | 6
2  | Name2 | QWSAASS   | A         | 9
2  | Name2 | QWSAASS   | B         | 1
2  | Name2 | QWSAASS   | D         | 8

2. 条件聚合生成独立列 #

基于中间结果，用MAX(CASE ...)做条件聚合，将不同spec_name转为独立列：

SELECT
  id,
  col1,
  MAX(CASE WHEN spec_name = 'A' THEN spec_value END) AS A,
  MAX(CASE WHEN spec_name = 'B' THEN spec_value END) AS B,
  MAX(CASE WHEN spec_name = 'C' THEN spec_value END) AS C,
  MAX(CASE WHEN spec_name = 'D' THEN spec_value END) AS D,
  spec_id
FROM (
  SELECT
    t.id,
    t.col1,
    col2->'spec_details'->>'spec_id' AS spec_id,
    spec_val->>'name' AS spec_name,
    spec_val->>'value' AS spec_value
  FROM your_table t,
       jsonb_array_elements(t.col2->'spec_details'->'spec_values') spec_val
) AS unfolded
GROUP BY id, col1, spec_id
ORDER BY id;

补充说明 #

• 若JSON字段类型为json，将所有jsonb_前缀替换为json_即可。
• 若后续会新增spec_name（如E、F），需手动添加对应的MAX(CASE ...)分支；PostgreSQL暂不支持动态透视，如需自动适配新字段，可结合PL/pgSQL编写存储过程实现。

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