PostgreSQL嵌套JSON结构扁平化：提取员工列表查询需求

解决方案 #

假设你的表名为your_table，JSON列名为json_column，可以使用以下查询直接提取并格式化输出所有员工编号：

SELECT '"' || json_array_elements_text(records->'emp_file'->'employees') || '"' AS employee_id
FROM your_table,
     json_array_elements(json_column->'employee_data'->'records') AS records;

逻辑说明 #

你之前使用json_array_elements未成功，是因为没有逐层拆解嵌套的JSON数组：

1. 拆解外层数组：先用json_array_elements(json_column->'employee_data'->'records')将employee_data下的records数组拆分为独立行，每行对应一个分组的记录对象。
2. 拆解内层员工数组：针对每个分组记录，提取emp_file下的employees数组，用json_array_elements_text将其拆分为单个员工编号的文本行。
3. 格式化输出：通过字符串拼接'"' || ... || '"'给员工编号添加双引号，匹配你需要的输出格式。

测试示例 #

如果需要验证效果，可以创建临时表测试：

-- 创建临时测试表
CREATE TEMP TABLE test_json (json_column json);
-- 插入示例数据
INSERT INTO test_json VALUES ('{  
"employee_data": {
    "records": [
      {
        "comment": "group1",
        "emp_file": {
          "employees": [
            "CNTA",
            "CNTB",
            "CNTC"
          ],
          "number_of_employees": 3
        }
      },
      {
        "comment": "group2",
        "emp_file": {
          "employees": [
            "CNTA",
            "CNTC"
          ],
          "number_of_employees": 2
        }
      }
    ]
  }
}');

-- 执行查询
SELECT '"' || json_array_elements_text(records->'emp_file'->'employees') || '"' AS employee_id
FROM test_json,
     json_array_elements(json_column->'employee_data'->'records') AS records;

执行后输出结果：

"CNTA"
"CNTB"
"CNTC"
"CNTA"
"CNTC"

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