在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'
            },
            ...
        }
    }
}

这是最终数据格式的要求： ```html

这是我需要的数据格式：

```

[
    {
        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多种技能。我的上面的查询有点疯狂。任何指导或帮助将不胜感激。

需要注意以下几点：

1. 用户对70多种技能进行了自我评估
2. 每个技能对象具有相同的结构
3. 每个用户对完全相同的技能集进行了自我评估

- MrDevinB

---

在你的数据结构中，我发现缺少了 user_id 字段。 - S-Man

更新：它们与数据对象处于同一级别。 - MrDevinB

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将这些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"
}]

- S-Man

---

这真是太棒了。这在9.6.6版本中能工作吗？我应该在我的原始问题中添加那个。对此我很抱歉。 - MrDevinB

是的，它适用于Postgres 9.6 http://sqlfiddle.com/#!17/9eecb/19326 请不要忘记接受并点赞。 - S-Man

谢谢，谢谢，谢谢。这正是我所需要的。你一定是一个Postgres巫师！！！你的魔法非常强大。 - MrDevinB

开玩笑的，其实只需要阅读文档 https://www.postgresql.org/docs/current/static/functions-json.html ，然后不断尝试直到成功。 - S-Man