Question 114462 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to parse multidimensional json array

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

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search