How to pass json array to postgresql function

Question

I have a json object contains array of json.

i.e:

{
    ID:'1',
    Name:'Pooja',
    School:[{Name:'ABC',Address:'Nagpur'},{Name:'CDF'},{Name:'GHI',Year:{From:'2015',To:'2016'}}]
}

I want to insert this in three different table as User, School and Year.

Can anyone help?

funbringer · Accepted Answer · 2016-02-03 12:05:05Z

3

Assuming I understood you correctly, you want to split the provided JSON object and write its subobjects to some tables.

PostgreSQL has several JSON operators that might help.

First of all, you should cast JSON's textual representation to type json. This allows you to use JSON operators and functions, such as -> (Get JSON object field):

select
'{
   "ID":1,
   "Name":"Pooja",
   "School":[
      {
         "Name":"ABC",
         "Address":"Nagpur"
      },
      {
         "Name":"CDF"
      },
      {
         "Name":"GHI",
         "Year":{
            "From":"2015",
            "To":"2016"
         }
      }
   ]
}'::json -> 'Name';

 ?column? 
----------
 Pooja
(1 row)

Or, for example, #> (Get JSON object at specified path):

select 'YOUR_JSON'::json #> '{"School", 2, "Year"}';

          ?column?          
----------------------------
 {                         +
             "From":"2015",+
             "To":"2016"   +
          }
(1 row)

All you have to do now is insert the result of the operator application into the table of your choice:

insert into user select 'YOUR_JSON'::json -> 'Name';

If you simply want to extract the School array, you could still use -> operator:

select 'YOUR_JSON'::json -> 'School';

          ?column?           
-----------------------------
 [                          +
       {                    +
          "Name":"ABC",     +
          "Address":"Nagpur"+
       },                   +
       {                    +
          "Name":"CDF"      +
       },                   +
       {                    +
          "Name":"GHI",     +
          "Year":{          +
             "From":"2015", +
             "To":"2016"    +
          }                 +
       }                    +
    ]
(1 row)

Read the documentation for more.

edited Feb 3, 2016 at 12:05

answered Feb 3, 2016 at 11:57

funbringer

961 silver badge5 bronze badges

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

2 Comments

Pooja-G Over a year ago

Thank you, I feel it will help me better.

Pooja-G Over a year ago

Thank you, My Problem is solved now. I have to add some more function in it . But it is in work now.

Collectives™ on Stack Overflow

How to pass json array to postgresql function

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related