postgres sql Query a field with Json data

Question

We have a field in a table that contains JSON object. There are array objects inside the json structure and we would have to extract values specifically from the array. Below is a sample data in the json field

 children:[{server:,children:[{server:,newPage:,menuType:3,label:File Tax Forms,url:\/dashboard\/fileTaxForm},{server:,newPage:,menuType:3,label:View Filed Forms,url:dashboard\/viewFiledForms}],

Can you please throw some light on how to extract the values via sql query in the above json object. Any help on this is much appreciated

Thanks in Advance

• user330315: Are you sure it's a column defined as jsonb or at least json? The content would look different in that case (mainly: all strings must be quoted with ")
• user330315: What exactly do you want to extract? Please edit your question and add the desired output.

Answer 1

When adding the json to your question, it seems to have lost quotes.

If the JSON is inserted as follows

INSERT INTO test VALUES ('{"children":[{"server":"","children":[{"server":"","newPage":"","menuType":3,"label":"File Tax Forms","url":"dashboard/fileTaxForm"},{"server":"","newPage":"","menuType":3,"label":"View Filed Forms","url":"dashboard/viewFiledForms"}]}]}');

into a table defined as follows:

CREATE TABLE test (somejson json); 

then you could use a query similar to the following:

SELECT somejson::json->'children'->0->'children'->0->'label' as label, somejson::json->'children'->0->'children'->0->'url' as url FROM test;

to get a result:

 label | url 
------------------+-------------------------
 "File Tax Forms" | "dashboard/fileTaxForm"