How to extract a particular element from a json array in postgres?

Question

I have a table parameter having 2 columns id(integer) and param_specs(text).

the actual param_specs column looks like above pic (to simplify it check below:- )

param_specs
[
{"paramName":"param1",
 "type":"string",
 "defaultValue":"tomcat7",
 "optional":false,
 "deploymentParam":false},    
{"paramName":"param123PreStopAction",
 "type":"path",
 "defaultValue":"HELLO",
 "optional":false,
 "deploymentParam":false}
]

So it is an array of json array and i want to fetch the defaultValue field of paramName param123PreStopAction i.e. HELLO.

****EDIT**** As can be seen in the image this is what my table called parameter looks like having two columns I want to get defaultValue of each row in parameter table where paramName LIKE (%PostStopAction) or (%PreStopAction) check the bold values in image(i.e. the paramName should have either PreStopAction or PostStopAction within the actual paramName value eg 'mytomcat7PostStopAction' and fetch its defaultValue i.e 'post-stop'.)

There can be some rows in the table where there wont be any json having preStop or PostStop paramName like row 3 in the image

can someone help me with the query?

As JGH suggested something as follows:-

SELECT "defaultValue" FROM parameter a CROSS JOIN LATERAL json_to_recordset(a.param_spec::json) AS x("paramName" text,"defaultValue" text) WHERE "paramName”LIKE “%PreStopAction' OR “paramName” LIKE “%PostStopAction”

JGH · Accepted Answer · 2017-05-29 17:52:08Z

1

One approach is to explode your array in fields and to query them. The trick is to consider only the fields of interest.

Select myOutputField 
from json_to_recordset('[the array]') as (myQueryField text, myOutputField text)
where myQueryField = myCondition;

Or, bound to your example:

select "defaultValue" from json_to_recordset('
[
{"paramName":"param1",
 "type":"string",
 "defaultValue":"tomcat7",
 "optional":false,
 "deploymentParam":false},    
{"paramName":"param123PreStopAction",
 "type":"path",
 "defaultValue":"HELLO",
 "optional":false,
 "deploymentParam":false}
]') as x("paramName" text,"defaultValue" text)
where "paramName" =  'param123PreStopAction';

** EDIT ** Your data is not saved in a json column but in a text column. You would have to convert it to json (ideally, the column itself... or at least its content). Also, the json_to_recordset works on single items, not on sets, so you would need to use a LATERAL JOIN to overcome this limitation, as nicely explained here.

SELECT myOutputField 
FROM mytable a
     CROSS JOIN LATERAL
      json_to_recordset(a.jsonintextcolumn::json) as (myQueryField text, myOutputField text)
WHERE myQueryField = myCondition;

Or, bound to your example:

SELECT "defaultValue" 
FROM public.testjsontxt a
     CROSS JOIN LATERAL
     json_to_recordset(a.param_specs::json) as x("paramName" text,"defaultValue" text)
WHERE "paramName" =  'param123PreStopAction';

edited May 29, 2017 at 17:52

answered May 29, 2017 at 15:31

JGH

18.2k5 gold badges40 silver badges57 bronze badges

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

7 Comments

100MIL Over a year ago

Hi JGH thanks mate though above solution works but instead of passing the json array in the inner query i want something like select "defaultValue" from json_to_recordset(' select param_specs::json from parameters) as x("paramName" text,"defaultValue" text) where "paramName" = 'param123PreStopAction'; Can you suggest something.

JGH Over a year ago

Please see the edit that makes use of the lateral join

100MIL Over a year ago

Thanks again JGH but on trying the query SELECT "defaultValue" FROM parameter a CROSS JOIN LATERAL json_to_recordset(a.param_specs::json) as x("paramName" text,"defaultValue" text) WHERE "paramName" = 'param123PreStopAction'; It fails by giving message (invalid input syntax for type json).Please note the table name is parameter.Any idea why i am facing this issue?

JGH Over a year ago

one (or more) of your input is not following the structure you have provided in the example

JGH Over a year ago

like having an empty string instead of NULL

|

Collectives™ on Stack Overflow

How to extract a particular element from a json array in postgres?

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related