PostgresSQL - Json Arrays into Rows

Question

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.

Why is grades an array? Will it always contain only one element? — Bergi
– Bergi, Commented Jun 22, 2024 at 7:18
Hi @Bergi, there will be more elements in grades. I have given only one for example. — Iniyavan
– Iniyavan, Commented Jun 22, 2024 at 7:39
Ok I updated my answer accordingly. Does it work for you? If not, what error did you get? What is the query you tried? — Bergi
– Bergi, Commented Jun 22, 2024 at 7:45
Hi @Bergi, I tried another row with multiple elements for grades . It works. This is the query I tried. SELECT sd.doc_id,grade ->> 'grade_name',student.* FROM xereports.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+' and sd.doc_id = 102; — Iniyavan
– Iniyavan, Commented Jun 22, 2024 at 8:33

Dogbert · Accepted Answer · 2024-06-22 07:18:52Z

1

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+

answered Jun 22, 2024 at 7:18

Dogbert

224k43 gold badges419 silver badges417 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Iniyavan Over a year ago

It works! I modified the query further to suit my requirement. Thanks!

Bergi · Accepted Answer · 2024-06-22 07:44:38Z

1

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

edited Jun 22, 2024 at 7:44

answered Jun 22, 2024 at 7:25

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

Collectives™ on Stack Overflow

PostgresSQL - Json Arrays into Rows

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related