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

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
– user330315, Commented Apr 1, 2021 at 5:56
What exactly do you want to extract? Please edit your question and add the desired output. — user330315
– user330315, Commented Apr 1, 2021 at 5:56

Stephan Schlecht · Accepted Answer · 2021-04-01 07:47:04Z

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"

answered Apr 1, 2021 at 7:47

Stephan Schlecht

27.2k1 gold badge36 silver badges51 bronze badges

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

Collectives™ on Stack Overflow

postgres sql Query a field with Json data

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related