How to parse multidimensional json array

Question

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

The first error is that you are storing JSON in a text column. That column should be defined as jsonb — user330315
– user330315, Commented Sep 26, 2022 at 11:35
The second error is that you store it in a JSON to begin with. The third error is that you don't store it as {"Registered": 123456789}. — Laurenz Albe
– Laurenz Albe, Commented Sep 26, 2022 at 11:38

user330315 · Accepted Answer · 2022-09-26 12:05:11Z

1

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

answered Sep 26, 2022 at 12:05

user330315

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

2 Comments

executable Over a year ago

Needed to add CAST(data AS jsonb) because my column is as VARCHAR

user330315 Over a year ago

@executable: yes, annoying casts are required if you don't use the proper data type when defining the table.

Stefanov.sm · Accepted Answer · 2022-09-26 12:13:18Z

1

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

edited Sep 26, 2022 at 12:13

answered Sep 26, 2022 at 12:07

Stefanov.sm

13.3k2 gold badges25 silver badges24 bronze badges

Collectives™ on Stack Overflow

How to parse multidimensional json array

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related