CODEWITHSUNDEEP
postgresqljson-extract
Iniyavan
Iniyavan

Reputation: 113

PostgresSQL - Json Arrays into Rows

I have a students_data table with a json column like this.

CREATE TABLE students_data (doc_id INT, doc_data JSON);

A row is inserted with doc_id = 101. The json content in the row is:

{
  "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+"
            }
          ]
        }
      }
    ]
  }
}

Now I need to get the data of all the AA+ students from this row as below. I tried with json_array_elements, json_to_record, json_to_recordset and unnest. I am unable to get the expected result.

enter image description here

Upvotes: 0

Views: 60

Answers (2)

Bergi
Bergi

Reputation: 665344

Combine json_array_elements, json_each and 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+';

Upvotes: 1

Dogbert
Dogbert

Reputation: 222388

Here's one way to do this, with a combination of 2 json_array_elements and one json_each (for the sections object):

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+';

Output:

student_name avg_marks rank
Arun 85 AA+
Chitra 86 AA+
Elango 88 AA+

Upvotes: 1

Related Questions