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