How to parse multidimensional json array-CodePudding

I'm trying to retreive some specific data from a json stored in my database.

Here is my fidle : https://www.db-fiddle.com/f/5qZhsyddqJNej2NGj1x1hi/1

An exemple of a json string :

{
   "complexProperties":[
      {
         "properties":{
            "key":"Registred",
            "Value":"123456789"
         }
      },
      {
         "properties":{
            "key":"Urgency",
            "Value":"Total"
         }
      },
      {
         "properties":{
            "key":"ImpactScope",
            "Value":"All"
         }
      }
   ]
}

In this case I need to retreive the value of Registred which is 123456789

Here is the request I tried to retreive first all value:

SELECT CAST(data AS jsonb)::json->>'complexProperties'->'properties' AS Registred FROM jsontesting

Query Error: error: operator does not exist: text -> unknown

CodePudding user response：

You can use a JSON Path expression:

select jsonb_path_query_first(data, '$.complexProperties[*].properties ? (@.key == "Registred").Value')
from jsontesting;

This returns a jsonb value. If you need to convert that to a text value, use jsonb_path_query_first(...) #>> '{}'

Online example

CodePudding user response：

An alternative that first flattens the JSON field (the arrj subquery) and then performs an old-school select. Using your jsontesting table -

select (j -> 'properties' ->> 'Value') 
from 
(
 select json_array_elements(data::json -> 'complexProperties') as j 
 from jsontesting
) as arrj
where j -> 'properties' ->> 'key' = 'Registred';

Online example