title: How to extract a particular element from a json array in postgres?
body: <p><a href="https://i.stack.imgur.com/4Z9iW.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/4Z9iW.png" alt="Table parameter image"></a>I have a table parameter having 2 columns id(integer) and param_specs(text).</p>
  
  <p>the actual param_specs column looks like above pic (to simplify it check below:-
  )</p>
  
  <pre><code>param_specs
  [
  {"paramName":"param1",
   "type":"string",
   "defaultValue":"tomcat7",
   "optional":false,
   "deploymentParam":false},    
  {"paramName":"param123PreStopAction",
   "type":"path",
   "defaultValue":"HELLO",
   "optional":false,
   "deploymentParam":false}
  ]
  </code></pre>
  
  <p>So it is an array of json array and i want to fetch the <code>defaultValue</code> field of <code>paramName</code> <code>param123PreStopAction</code> i.e. HELLO.</p>
  
  <p>****EDIT****
  As can be seen in the image this is what my <strong>table called parameter</strong> looks like having two columns  I want to get <strong>defaultValue</strong> 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 'mytomcat7<strong>PostStopAction</strong>'  and fetch its <strong>defaultValue</strong> i.e '<strong>post-stop</strong>'.)</p>
  
  <p>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 </p>
  
  <p>can someone help me with the query?</p>
  
  <p>As JGH suggested something as follows:-</p>
  
  <p><strong>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”</strong> </p>