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.

GMB · Accepted Answer · 2020-11-18 20:25:43Z

2

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'
)

answered Nov 18, 2020 at 20:25

GMB

224k25 gold badges103 silver badges151 bronze badges

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

1 Comment

work_in_progress Over a year ago

works like a charm. Thank you. I was unaware of the unnest function. Ref: postgresql.org/docs/12/functions-array.html.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related