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.

Answer 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+