时间戳

首页 > 解决方案 > 如何从 postgres jsonb 列中选择特定对象

postgresql - 如何从 postgres jsonb 列中选择特定对象

问题描述

我将学生的评估存储在 jsonb 列调用评估中，数据库中的每一行都存储学生对特定学科的评估，并且我试图获取有助于检索的查询： 1. 特定学生的评估主题。2. 学生提供的每门学科的所有学生评估。

这是我从表中选择所有记录时得到的结果。

[

     {
        "id": "22670f29-1437-4af1-b907-f6940377a851",
        "sessionId": 3,
        "sectionId": 4,
        "subjectId": 8,
        "assessment": [
            {
                "exam": 50,
                "grdae": "A",
                "total": 79,
                "position": "First",
                "student_id": 2,
                "assessment_1": 9,
                "assessment_2": 17,
                "assessment_4": 5,
                "student_name": "Anana Aristotle"
            },
            {
                "exam": 50,
                "grdae": "B",
                "total": 69,
                "position": "Third",
                "student_id": 3,
                "assessment_1": 9,
                "assessment_2": 17,
                "assessment_4": 5,
                "student_name": "Anana Elvis"
            }
        ]
    },
    {
        "id": "beca9d97-0d0a-4d89-b8c5-c9a6fed812a5",
        "sessionId": 3,
        "sectionId": 4,
        "subjectId": 10,
        "assessment": [
            {
                "exam": 50,
                "grdae": "A",
                "total": 79,
                "position": "First",
                "student_id": 2,
                "assessment_1": 9,
                "assessment_2": 17,
                "assessment_4": 5,
                "student_name": "Anana Aristotle"
            },
            {
                "exam": 50,
                "grdae": "B",
                "total": 69,
                "position": "Third",
                "student_id": 3,
                "assessment_1": 9,
                "assessment_2": 17,
                "assessment_4": 5,
                "student_name": "Anana Elvis"
            }
        ]
    },
    {
        "id": "1ed9c3d6-2482-4b1d-ab59-b77a8933699c",
        "sessionId": 3,
        "sectionId": 4,
        "subjectId": 9,
        "assessment": [
            {
                "exam": 50,
                "grdae": "A",
                "total": 90,
                "position": "First",
                "student_id": 2,
                "assessment_1": 9,
                "assessment_2": 17,
                "assessment_4": 5,
                "student_name": "Anana Aristotle"
            },
            {
                "exam": 50,
                "grdae": "B",
                "total": 69,
                "position": "Third",
                "student_id": 3,
                "assessment_1": 9,
                "assessment_2": 17,
                "assessment_4": 5,
                "student_name": "Anana Elvis"
            }
        ]
    }
]

此结果包含对不同科目的评估，其中两个学生的科目 ID 分别为 8、9 和 10，ID 为 2 和 3。

注意：sessionID和SectionID也是subjectId表调用评估中的列。

如果我想要一个 id 为 2 的学生评估的 subjectId 为 9 且 sessionId 为 3 并且 sectionId 为 4，我应该能够得到这个对象作为回报：

         {
            "exam": 50,
            "grdae": "A",
            "total": 90,
            "position": "First",
            "student_id": 2,
            "assessment_1": 9,
            "assessment_2": 17,
            "assessment_4": 5,
            "student_name": "Anana Aristotle"
         }

如果我想要一个对所有三个 subjectId 进行 id 2 评估的学生，即 id 8、9、10，并且 sessionId 为 3，sectionId 为 4，我应该得到这个结果：

[
         {
            "exam": 50,
            "grdae": "A",
            "total": 90,
            "position": "First",
            "student_id": 2,
            "assessment_1": 9,
            "assessment_2": 17,
            "assessment_4": 5,
            "student_name": "Anana Aristotle"
         },
         {
            "exam": 50,
            "grdae": "A",
            "total": 79,
            "position": "First",
            "student_id": 2,
            "assessment_1": 9,
            "assessment_2": 17,
            "assessment_4": 5,
            "student_name": "Anana Aristotle"
        },
        {
            "exam": 50,
            "grdae": "A",
            "total": 79,
            "position": "First",
            "student_id": 2,
            "assessment_1": 9,
            "assessment_2": 17,
            "assessment_4": 5,
            "student_name": "Anana Aristotle"
        }
]

标签： postgresqljsonb

解决方案

将小提琴中的数据最小化为：

[

     {
        "sessionId": 3,
        "sectionId": 4,
        "subjectId": 8,
        "assessment": [
            {              
                "student_id": 2
            },
            {
                "student_id": 3
            }
        ]
    },
    {
        "sessionId": 3,
        "sectionId": 4,
        "subjectId": 10,
        "assessment": [
            {
                "student_id": 2
            },
            {
                "student_id": 3
            }
        ]
    },
    {
        "sessionId": 3,
        "sectionId": 4,
        "subjectId": 9,
        "assessment": [
            {
                "student_id": 2
            },
            {
                "student_id": 3
            }
        ]
    }
]

演示：db<>小提琴

第一步是取消嵌套相关的 id 信息：

SELECT
    assessments,
    (elems ->> 'sessionId')::int as session_id,
    (elems ->> 'sectionId')::int as section_id,
    (elems ->> 'subjectId')::int as subject_id,
    (assessments ->> 'student_id')::int AS student_id
FROM
    mytable,
    json_array_elements(jsondata) as elems,
    json_array_elements(elems -> 'assessment') as assessments

这导致：

assessments      | session_id | section_id | subject_id | student_id
:--------------- | ---------: | ---------: | ---------: | ---------:
{"student_id":2} |          3 |          4 |          8 |          2
{"student_id":3} |          3 |          4 |          8 |          3
{"student_id":2} |          3 |          4 |         10 |          2
{"student_id":3} |          3 |          4 |         10 |          3
{"student_id":2} |          3 |          4 |          9 |          2
{"student_id":3} |          3 |          4 |          9 |          3

该json_array_elements()函数将 json 数组嵌套到每个数组元素的一条记录中。因为有两个嵌套数组，所以需要调用这个函数两次。您可以在SELECT列表中看到每个级别的值。

之后，您可以在WHERE子句中过滤您想要的任何内容。结果记录（相关评估对象）可以使用json_agg()聚合函数进行分组：

SELECT 
    json_agg(assessments)
FROM (
    -- query from above
) s
WHERE session_id IN (3)
    AND section_id IN (4)
    AND subject_id IN (9) -- only one subject
    AND student_id IN (2)

或者

SELECT 
    json_agg(assessments)
FROM (
    -- query from above
) s
WHERE session_id IN (3)
    AND section_id IN (4)
    AND subject_id IN (8,9,10) -- more subjects
    AND student_id IN (2)

推荐阅读