代码之家 › 专栏 › 技术社区 › Iniyavan

PostgresSQL-Json数组成行

json-extract postgresql

0

Iniyavan · 技术社区 · 1 年前

我有一个 students_data 带有的表 json 像这样的专栏。

CREATE TABLE students_data (doc_id INT, doc_data JSON);

插入一行时 doc_id = 101 。行中的json内容为:

{
  "document_type": "students_report",
  "document_name": "students_report_202406.pdf",
  "data": {
    "grades": [
      {
        "sections": {
          "1A_students": [
            {
              "student_name": "Arun",
              "avg_marks": 85,
              "rank": "AA+"
            },
            {
              "student_name": "Bala",
              "avg_marks": 70,
              "rank": "A+"
            }
          ],
          "1B_students": [
            {
              "student_name": "Chitra",
              "avg_marks": 86,
              "rank": "AA+"
            },
            {
              "student_name": "David",
              "avg_marks": 72,
              "rank": "A+"
            }
          ],
          "1C_students": [
            {
              "student_name": "Elango",
              "avg_marks": 88,
              "rank": "AA+"
            },
            {
              "student_name": "Fathima",
              "avg_marks": 74,
              "rank": "A+"
            }
          ]
        }
      }
    ]
  }
}

现在我需要获取所有 AA+ 这一行的学生如下。我试过了 json_array_elements , json_to_record , json_to_recordset 和 unnest 。我无法得到预期的结果。

2 回复 | 直到 1 年前

1

Bergi 1 年前

结合 json_array_elements , json_each 和 json_to_recordset :

SELECT student.*
FROM
  students_data sd,
  json_array_elements(sd.doc_data->'data'->'grades') as grade,
  json_each(grade->'sections') as section(name, students),
  json_to_recordset(section.students) as student(student_name text, avg_marks int, rank text) 
WHERE
  student.rank = 'AA+';

2

1

Dogbert 1 年前

这里有一种方法,使用2 json_array_elements 还有一个 json_each (对于 sections 对象

select
    student->>'student_name' as student_name,
    student->>'avg_marks' as avg_marks,
    student->>'rank' as rank
from
    students_data,
    json_array_elements(doc_data->'data'->'grades') as grade,
    json_each(grade->'sections') as section(key, value),
    json_array_elements(section.value) as student
where
    student->>'rank' = 'AA+';

输出

学生名称	平均标记(_M)	等级
阿伦	85	AA+
奇特拉	86	AA+
Elango	88	AA+