json - 在 PostgreSQL 中展平嵌套的 JSON 结构

Question

我正在尝试编写一个 Postgres 查询，它将以特定格式输出我的 json 数据。

JSON 数据结构

{
    user_id: 123,
    data: {
        skills: {
            "skill_1": {
                "title": "skill_1",
                "rating": 4,
                "description": 'description text'
            },
            "skill_2": {
                "title": "skill_2",
                "rating": 2,
                "description": 'description text'
            },
            "skill_3": {
                "title": "skill_3",
                "rating": 5,
                "description": 'description text'
            },
            ...
        }
    }
}

这就是我最终需要格式化数据的方式:

[
    {
        user_id: 123,
        skill_1: 4, 
        skill_2: 2, 
        skill_3: 5, 
                    ... 
    },
    {
        user_id: 456,
        skill_1: 1, 
        skill_2: 3, 
        skill_3: 4, 
                    ... 
    }
]

到目前为止，我正在使用如下所示的查询:

SELECT
    user_id,
    data#>>'{skills, "skill_1",  rating}' AS "skill_1",
    data#>>'{skills, "skill_2",  rating}' AS "skill_2",
    data#>>'{skills, "skill_3",  rating}' AS "skill_3"
FROM some_table

必须有更好的方法来编写我的查询。有400+行和70+技能。我上面的查询有点疯狂。任何指导或帮助将不胜感激。

一些注意事项:

用户对自己的 70 多项技能进行评分

每个技能对象都具有相同的结构

每个用户在完全相同的技能集上给自己评分

Answer 1

db<>fiddle

我将您的测试数据扩展为(注意所有用户周围的数组):

[{
    "user_id": 123,
    "data": {
        "skills": {
            "skill_1": {
                "title": "skill_1",
                "rating": 4,
                "description": "description text"
            },
            "skill_2": {
                "title": "skill_2",
                "rating": 2,
                "description": "description text"
            },
            "skill_3": {
                "title": "skill_3",
                "rating": 5,
                "description": "description text"
            }
        }
    }
},
{
    "user_id": 456,
    "data": {
        "skills": {
            "skill_1": {
                "title": "skill_1",
                "rating": 1,
                "description": "description text"
            },
            "skill_2": {
                "title": "skill_2",
                "rating": 3,
                "description": "description text"
            },
            "skill_3": {
                "title": "skill_3",
                "rating": 4,
                "description": "description text"
            }
        }
    }
}]

查询:

SELECT 
    jsonb_pretty(jsonb_agg(user_id || skills))               -- E
FROM (
    SELECT
        json_build_object('user_id', user_id)::jsonb as user_id,  -- D
        json_object_agg(skill_title, skills -> skill_title -> 'rating')::jsonb as skills
    FROM (
        SELECT 
            user_id,
            json_object_keys(skills) as skill_title,         -- C
            skills
        FROM (
            SELECT
                (datasets -> 'user_id')::text as user_id,
                datasets -> 'data' -> 'skills' as skills     -- B
            FROM (
                SELECT 
                  json_array_elements(json) as datasets      -- A
                FROM (
                  SELECT '/* the JSON data; see db<>fiddle */'::json
                )s
            )s
        )s  
    )s    
    GROUP BY user_id
    ORDER BY user_id
)s

A 使所有数组元素 ( {user_id: '42', data: {...}} ) 各占一行

B 第一列安全的 user_id 。之后的 GROUP BY 需要转换为文本，它不能对 JSON 输出进行分组。对于第二列，提取用户的 skills 数据

C 提取技能标题，将它们用作(D.1)中的键。

D.1 skills -> skill_title -> 'rating' 提取每个技能的评分值

D.2 json_object_agg 将skill_titles 和每个对应的评分值聚合成一个JSON 对象；按 user_id 分组

D.3 json_build_object 使 user_id 再次成为 JSON 对象

E.1 user_id || skills 将两个json对象聚合为一个

E.2 jsonb_agg aggregates 将这些json对象编成数组

E.3 jsonb_pretty 使结果看起来很漂亮。

结果:

[{
    "skill_1": 4,
    "skill_2": 2,
    "skill_3": 5,
    "user_id": "123"
},
{
    "skill_1": 1,
    "skill_2": 3,
    "skill_3": 4,
    "skill_4": 42,
    "user_id": "456"
}]