如何在PostgreSQL中将指定JSON数据转换为行列形式展示

需求说明 #

给定嵌套JSON数据：

[
    {
        "name": "Ali", 
        "summaries": 
        [
            {"claim": "a" , "balance": 1}, 
            {"claim": "b" , "balance": 1}
        ]
    },
    {
        "name": "Abu", 
        "summaries": 
        [
            {"claim": "a" , "balance": 1}, 
            {"claim": "b" , "balance": 1}
        ]
    }
]

需转换为如下行列结构：

实现方法 #

1. 数据存储在表中的场景

假设已创建表test_data并存储上述JSON：

-- 创建测试表
CREATE TABLE test_data (json_col JSON);

-- 插入JSON数据
INSERT INTO test_data VALUES ('[
    {
        "name": "Ali", 
        "summaries": 
        [
            {"claim": "a" , "balance": 1}, 
            {"claim": "b" , "balance": 1}
        ]
    },
    {
        "name": "Abu", 
        "summaries": 
        [
            {"claim": "a" , "balance": 1}, 
            {"claim": "b" , "balance": 1}
        ]
    }
]');

执行以下查询获取目标结果：

SELECT
    LOWER(obj ->> 'name') AS name,
    obj -> 'summaries' -> 0 AS summaries
FROM
    test_data,
    json_array_elements(json_col) AS obj;

2. 直接处理JSON字符串的场景

无需创建表，直接对JSON字符串进行处理：

SELECT
    LOWER(obj ->> 'name') AS name,
    obj -> 'summaries' -> 0 AS summaries
FROM
    json_array_elements('[
    {
        "name": "Ali", 
        "summaries": 
        [
            {"claim": "a" , "balance": 1}, 
            {"claim": "b" , "balance": 1}
        ]
    },
    {
        "name": "Abu", 
        "summaries": 
        [
            {"claim": "a" , "balance": 1}, 
            {"claim": "b" , "balance": 1}
        ]
    }
]'::JSON) AS obj;

3. 输出与示例完全一致的字符串格式（可选）

如果需要将summaries字段转换为不带双引号键的字符串格式，可使用字符串替换：

SELECT
    LOWER(obj ->> 'name') AS name,
    REPLACE(REPLACE(obj -> 'summaries' ->> 0, '"claim"', 'claim'), '"balance"', 'balance') AS summaries
FROM
    test_data,
    json_array_elements(json_col) AS obj;

内容的提问来源于stack exchange，提问作者Md Fauzan Bin Md Asri