Query elements in a nested array of a json object in postgresql 9.4 or 9.5

Question

{
  "studentID": 1,
  "StudentName": "jhon",
   "Data":{
     "schoolname":"school1",
     "enrolmentInfo":
           [{  
              "year":"2015",
              "info": 
                    [
                     {
                      "courseID":"csc213",
                      "school":"IT",
                      "enrollmentdate":"2015-01-01",
                      "finshdate":"2015-07-01",
                      "grade": 80 },

                      {
                      "courseID":"csc113",
                      "school":"IT1",
                      "enrollmentdate":"2015-09-02",
                      "finshdate":null,
                      "grade": 90 } ]
               },
             {  
              "year":"2014",
              "info": 
                    [{
                      "courseID":"info233",
                      "school":"IT",
                      "enrollmentdate":"2014-03-11",
                      "finshdate":"2014-09-01",
                      "grade": 81 },

                      {
                      "courseID":"csc783",
                      "school":"IT",
                      "enrollmentdate":"2014-01-02",
                      "finshdate":"2014-08-01",
                      "grade": 87 } ]
              }  ]
        }

     }

I have stored in postgresql database json objects of the above format. Each object consists of informations about a certain student with enrollment information. I have complex objects with nested array inside arrays. I am trying to select all the element inside the "info" array. I tried to use the following query:

 with recursive x (info) as (select value->'info' from jsontesting r,  json_array_elements(r.data->'Data'->'enrolmentinfo') 
 UNION ALL 
 SELECT  (e).value->'courseID', (e).value->'school', (e).value->'grade',(e).value->'enrollmentdate', (e).value->'finshdate'   
 from (select json_each(json_array_elements (info)) e from x) p)
 select * from x;

This query is not working and it is giving the following error:"cannot call json_array_elements on a scalar". Is there any other query that I can use to extract the elements of the nested array "info"??

json.net is specifically for the .Net library Json.NET. Since you don't seem to be using this library, I removed the tag. — dbc
– dbc, Commented Dec 1, 2015 at 3:40
Please provide a valid json value. This one has multiple syntax errors, missing double quotes and commas AFAICS. And why do you tag [postgres-9.3] while asking for 9.4 or 9.5? — Erwin Brandstetter
– Erwin Brandstetter, Commented Dec 1, 2015 at 4:48

Dzmitry Savinkou · Accepted Answer · 2015-12-01 06:17:58Z

6

-- assuming that jsontesting.data contains your JSON    
WITH info_data AS (
   SELECT enrolment_info->'info' AS info
   FROM jsontesting t, json_array_elements(t.data -> 'Data' -> 'enrolmentInfo') AS enrolment_info
)
SELECT info_item->>'courseID',
       info_item->>'school',
       info_item->>'enrollmentdate',
       info_item->>'finshdate',
       info_item->>'grade'
FROM info_data idata, json_array_elements(idata.info) AS info_item;

answered Dec 1, 2015 at 6:17

Dzmitry Savinkou

5,2102 gold badges28 silver badges32 bronze badges

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

6 Comments

R.Y.H Over a year ago

Thank you, It is working well. I have small question: If we have inside the array element another set of elements. for example, courseID:{ coursename:"computer science", coursecode: "csc213" }, how can we select coursename and coursecode??

Dzmitry Savinkou Over a year ago

if you have nested doc you can use -> notation - info_item ->'courseID'->>'coursename'

R.Y.H Over a year ago

I want to select year with the info,I tried this: WITH info_data AS ( SELECT enrolment_info->'info' AS info, enrolment_info->’year’ as yr FROM jsontesting t, json_array_elements(t.data -> 'Data' -> 'enrolmentInfo') AS enrolment_info ) SELECT years, info_item->>'courseID', info_item->>'school', info_item->>'enrollmentdate', info_item->>'finshdate', info_item->>'grade' FROM info_data idata, json_array_elements(idata.info) AS info_item, to_json(idata.yr) as years; @Dmitry

R.Y.H Over a year ago

This query is working fine when the “info” array is not empty, but in some cases I have the year with out info but I want the year. Example: "enrolmentInfo":[{ "year":"2015", "info": []}] In this case the query is not returning the year. How can I return the yearif “info” array is empty.@ Dmitry

Dzmitry Savinkou Over a year ago

@R.Y.H could you please update the question with additional JSON structure?

|

Collectives™ on Stack Overflow

Query elements in a nested array of a json object in postgresql 9.4 or 9.5

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related