CODEWITHSUNDEEP
arraysjsonpostgresql
100MIL
100MIL

Reputation: 157

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

Table parameter imageI 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”

Upvotes: 2

Views: 4627

Answers (1)

JGH
JGH

Reputation: 17906

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';

Upvotes: 1

Related Questions