PostgreSQL（DBeaver）将JSON列嵌套对象拆分为多列的SQL方法问询

你的数据集某列为JSON格式，结构示例：

{"abcde1":"INFO","abcde2":"MOTOR","abcde3":{"COLOR":"WHITE","DATE_BIRTH":"05/09/1992","GENDER":"F"},"PARTNER_ID":"XYZOEKF","NAME":"ANYTHING","userId":"1204923"}

要提取abcde3内的字段转为独立列，你之前的SQL存在两个关键问题：

1. 用单引号包裹json_column_in_dataset，导致它被识别为固定字符串而非表中的实际列
2. 直接访问COLOR等字段，忽略了这些字段嵌套在abcde3对象内的层级

方法一：直接层级访问 #

无需CTE，直接从原表通过JSON操作符提取嵌套字段：

SELECT
  -- 使用->返回JSON类型，->>返回文本类型，按需选择
  json_column_in_dataset -> 'abcde3' -> 'COLOR' AS color,
  json_column_in_dataset -> 'abcde3' ->> 'DATE_BIRTH' AS date_birth,
  json_column_in_dataset -> 'abcde3' ->> 'GENDER' AS gender,
  -- 按需添加其他abcde3内的字段
  -- 也可以保留原表的其他非JSON字段
  partner_id,
  name
FROM my_table
WHERE any_condition = true;

方法二：使用jsonb_to_record（适合多字段场景） #

如果abcde3内字段较多，可通过jsonb_to_record（JSON类型用json_to_record）一次性映射为关系型记录：

SELECT
  rec.color,
  rec.date_birth,
  rec.gender,
  -- 原表其他字段
  t.partner_id,
  t.name
FROM my_table t,
     jsonb_to_record(t.json_column_in_dataset -> 'abcde3') AS rec(
       color text,
       date_birth text,
       gender text
       -- 按实际字段定义名称和数据类型，字段顺序不影响
     )
WHERE t.any_condition = true;

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