skip to Main Content

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

2

Answers


  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+
    Login or Signup to reply.
  2. 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+';
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search