How to Query Nested Arrays in a Postgres JSON Column

How to query nested arrays in a postgres json column?

Use jsonb_array_elements() in lateral join as many times as the depth of a json array which elements you want to compare:

select 
    schools->>'school_id' school_id,
    addresses->>'addr_id' addr_id,
    addresses->>'house_description' house_description,
    addresses->>'house_no' house_no
from title_register_data,
jsonb_array_elements(address_data->'schools') schools,
jsonb_array_elements(schools->'addresses') addresses
where addresses->>'house_description' = addresses->>'house_no';

 school_id | addr_id | house_description | house_no 
-----------+---------+-------------------+----------
 1         | 4       | 1                 | 1
(1 row)  

Postgres - JSONB - Nested Columns - Query Nested Jsonb array column

tl;dr use the ?| operator.

---

There's two problems with your query.

->> returns text not jsonb. So you're asking if the text ["white", "asian"] matches white or african american.

You probably did that because otherwise you got type errors trying to use any with JSONB. any wants a Postgres array of things to compare, and it has to be an array of jsonb. We can do that...

select user_id
from user
where profile -> 'data' -> 'races' = ANY(array['"white"', '"african american"']::jsonb[]);

But this has the same problem as before, it's checking if the json array [ "white", "asian" ] equals "white" or "african american".

You need an operator which will match against each element of the JSON. Use the ?| operator.

select user_id
from users
where profile -> 'data' -> 'races' ?| array['white', 'african american'];

Querying nested JSON arrays in PostgreSQL

I assumed you want to get id of row where exists array element with mentionned values. sample:

t=# create table so40 (i int, j json);
CREATE TABLE
t=# insert into so40 select 1,'{
t'#   "modes": [
t'#     {
t'#       "params": [
t'#         {"name": "x", "value": 10},
t'#         {"name": "y", "value": 15}
t'#       ]
t'#     },
t'#     {
t'#       "params": [
t'#         {"name": "x", "value": 20},
t'#         {"name": "y", "value": 25}
t'#       ]
t'#     }
t'#   ]
t'# }';
INSERT 0 1

select:

with f as (
        with t as (
                with j as (select * from so40)
                select *,json_array_elements(j->'modes')->'params' p from j
        )
        select *,json_array_elements(p)->>'name' n,json_array_elements(p)->>'value' v from t
)
select i,j,n,v
from f
where  n  ='x' and v::int between 15 and 25
;
 i |                  j                  | n | v
---+-------------------------------------+---+----
 1 | {                                  +| x | 20
   |   "modes": [                       +|   |
   |     {                              +|   |
   |       "params": [                  +|   |
   |         {"name": "x", "value": 10},+|   |
   |         {"name": "y", "value": 15} +|   |
   |       ]                            +|   |
   |     },                             +|   |
   |     {                              +|   |
   |       "params": [                  +|   |
   |         {"name": "x", "value": 20},+|   |
   |         {"name": "y", "value": 25} +|   |
   |       ]                            +|   |
   |     }                              +|   |
   |   ]                                +|   |
   | }                                   |   |
(1 row)

Efficiently querying JSON with nested arrays in Postgres

Did you try using the IN operator?

WHERE 'PBR' IN (
  SELECT jsonb_array_elements(jsonb_array_elements(data -> 'drinkers') -> 'beers') ->> 'name'
);

Another approach would be to use JSONB contain (i.e. @>):

WHERE data -> 'drinkers' @> '[{"beers": [{"name": "PBR"}]}]';

Postgres find in jsonb nested array

You don't need any nesting, you can do lateral queries:

SELECT
  city->>'city_name' AS city,
  (city->>'population')::INTEGER AS population
FROM
  mydata,
  JSONB_ARRAY_ELEMENTS(data_column->'continents') AS continent,
  JSONB_ARRAY_ELEMENTS(continent->'countries') AS country,
  JSONB_ARRAY_ELEMENTS(country->'cities') AS city
WHERE continent ->> 'name' = 'Europa'
  AND country ->> 'country_name' = 'Norway'
  AND city ->> 'city_name' = 'Oslo';

_{(online demo)}

However, since you mentioned paths and having to specify indices in there, this is actually the perfect use case for Postgres 12 JSON paths:

SELECT jsonb_path_query(data_column, '$.continents[*]?(@.name == "Europa").countries[*]?(@.country_name=="Norway").cities[*]?(@.city_name=="Oslo")') FROM mydata

_{(online demo)}

How to get field from postgres jsonb nested array

Here is my suggestion. You can of course add a where and other clauses.

with t(x) as 
(
 select j from jsonb_array_elements('... put your JSON data here ...'::jsonb) j
)
select (e ->> 'price')::numeric, (e ->> 'dish_modification_id')::numeric 
  from t 
 cross join lateral jsonb_array_elements(t.x -> 'modifications') e;

Another (maybe cleaner) version. First flatten and then select.

with t(jsondata) as 
(
 select '... put your JSON data here ...'::jsonb
)
 select (e ->> 'price')::numeric, (e ->> 'dish_modification_id')::numeric 
   from t 
  cross join lateral jsonb_array_elements(jsondata) ext_array
  cross join lateral jsonb_array_elements(ext_array -> 'modifications') e;

Running examples here.

---

Related Topics