0

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
  • 216,147
  • 25
  • 84
  • 135
work_in_progress
  • 747
  • 1
  • 10
  • 27

1 Answers1

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'
)
GMB
  • 216,147
  • 25
  • 84
  • 135
  • 1
    works like a charm. Thank you. I was unaware of the unnest function. Ref: https://www.postgresql.org/docs/12/functions-array.html. – work_in_progress Nov 18 '20 at 20:33