Postgres query json array to find the count which matches the condition

Question

I have a table my_table with a column name itinerary in my Postgres 12 DB.

 select column_name, data_type from information_schema.columns where table_name = 'my_table' and column_name = 'itinerary';
 column_name | data_type
-------------+-----------
 itinerary   | ARRAY
(1 row)

Every element in the itinerary is a JSON with the field name address, which has the field name city. I can find the count which matches the condition for the first element of the itinerary by using the following query:

select count(*) from my_table where lower(itinerary[1]->'address'->>'city') = 'oakland';
count
-------
    12
(1 row)

and I can also find the length of an array by using the following query:

select array_length(itinerary, 1) from my_table limit 1;

I would like to find all the records which can have a city name Oakland in their itinerary, not only as a first stop. I am not sure how to figure out that. Thanks in advance.

Answer 1

You can use exists and unnest():

select count(*)
from mytable t
where exists (
    select 1
    from unnest(t.itinerary) as x(obj)
    where x.obj -> 'address'->>'city' = 'oakland'
)