How to get data from postgresql json array field in an array

Question

Below is a json data in a column name meta and table name layer. Here i retrieve data of 'pages' key by meta->'pages' but i have no idea how to get 'lable' key value which is an array element of 'fields' which is again in an array pages.

{
   "id":1,
   "name":"org_details",
   "action":"organisation.php",
   "lable":"Manage Organisation",
   "pages":[
      {
         "name":"Create Org",
         "lable":"Organisation Name",
         "fields":[
            {
               "id":11,
               "type":1,
               "subtype":1,
               "lable":"Organisation Name"
            },
            {
               "id":12,
               "type":2,
               "subtype":1,
               "lable":"Description",
               "mandatory":TRUE,
               "validations":{
                  "minl":2,
                  "maxl":60
               }
            },
            {
               "id":13,
               "type":3,
               "subtype":1,
               "lable":"Org. Type",
               "default value":1,
               "mandatory":TRUE,
               "choices":[
                  {
                     "lable":"OFSDP",
                     "value":1
                  },
                  {
                     "lable":"AGRICULTURE",
                     "value":2
                  },
                  {
                     "lable":"HUTICULTURE",
                     "value":3
                  }
               ]
            },
            {
               "id":14,
               "type":4,
               "lable":"checkbox",
               "default value":1
            },
            {
               "id":15,
               "type":5,
               "subtype":1,
               "lable":"Upload",
               "mandatory":TRUE
            },
            {
               "id":16,
               "type":6,
               "subtype":1,
               "lable":"GIS"
            },
            {
               "id":17,
               "type":7,
               "subtype":1,
               "lable":"Date"
            },
            {
               "id":18,
               "type":8,
               "lable":"Attachment"
            }
         ]
      }
   ]
}

Postgre documentation is some of the best i've seen. JSON page here. postgresql.org/docs/current/static/functions-json.html — Gurmokh
– Gurmokh, Commented Aug 16, 2017 at 9:28
My postgres doesn't accept TRUE, had to replace it with "TRUE" or true — Andomar
– Andomar, Commented Aug 16, 2017 at 9:39

Oto Shavadze · Accepted Answer · 2017-08-16 10:01:02Z

6

One way with json_array_elements:

(assuming that your table is your_table and json column name is meta)

select j.value->>'lable'
from your_table
join lateral json_array_elements(meta->'pages'->0->'fields') j
on true

edited Aug 16, 2017 at 10:01

answered Aug 16, 2017 at 9:50

Oto Shavadze

43.3k56 gold badges168 silver badges248 bronze badges

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

Comments

Andomar · Accepted Answer · 2017-08-16 10:34:07Z

3

You can index an array by number:

select meta::jsonb->'pages'->0->'lable'
from   layer

To retrieve the "lables" of all organisations, create a table with jsonb_to_recordset:

select  orgs.lable
from    layer
cross join
        jsonb_to_recordset(meta::jsonb->'pages') orgs(name text, lable text)

The second argument to jsonb_to_recordset defines the columns you are interested in. Here I'm using orgs(name text, lable text) to make the name and lable available.

To get the value of a nested JSON dictionary, you can use a lateral join:

select  orgs.lable
,       fields.lable
from    layer
cross join
        jsonb_to_recordset(meta::jsonb->'pages')
            orgs(lable text, fields jsonb)
cross join
        jsonb_to_recordset(fields) fields(lable text)

Working example at regtester.

edited Aug 16, 2017 at 10:34

answered Aug 16, 2017 at 9:39

Andomar

239k55 gold badges387 silver badges412 bronze badges

3 Comments

Avinash Over a year ago

want to retrieve values : Organisation Name, Description, Org. Type, checkbox, Upload, GIS, Date, Attachment

Avinash Over a year ago

what do you mean by <your json here>

Andomar Over a year ago

Replaced <your json here> with a select on the layer table

Charlie 木匠 · Accepted Answer · 2021-03-31 20:00:36Z

1

select '[
{
            "item_type"       : "FEE"
            ,"item_id": 18
            , "item_status"     : "PENDING"
            , "description"          : "auto_pay"
}
]'::json->0->>'item_id' as item_id;

 item_id 
--------------
 18

answered Mar 31, 2021 at 20:00

Charlie 木匠

2,43022 silver badges20 bronze badges

Collectives™ on Stack Overflow

How to get data from postgresql json array field in an array

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related