sql - Postgres 9.5 Query matching JSON Property within Array -

i have column address, type jsonb[], within table houses contains array looking this:

"{   "{\"zip\": \"13203-1807\",    \"city\": \"\syracuse\",    \"state\": \"new york\",    \"street\": \"\999 prospect avenue\"}"  }" 

i trying query houses address city "syracuse"; far have:

select * houses address -> 'city' = 'syracuse' 

and receive error:

no operator matches given name , argument type(s). might need add explicit type casts. 

i have looked through pg official documentation on json functions , operators several stackoverflow answers no avail.

okay, mentioned in comments above, you're trying use json operator on postgresql array, why you're getting type error. there few ways can approach this, depending on want store in field.

for these examples, i'm going assume following table:

create table houses (id int, address <some datatype>); 

if want postgres array of jsonb, have unpack array, can so:

with all_addresses (   select id, unnest(address) add     houses) select *   houses h       id in (select id              all_addresses              add->'city' = to_jsonb('syracuse'::text)); 

if, on other hand, want use json array, query might (very similar, since still need unnest array):

with all_addresses (   select id, jsonb_array_elements(address) add     houses) select *   houses h       id in (select id              all_addresses              add->'city' = to_jsonb('syracuse'::text)); 

i can't quite tell question, it's possible you're ever storing 1 address per row in column (based on example data , fact column named 'address', not 'addresses'). if that's case, query gets simpler:

select *   houses   address->'city' = to_jsonb('syracuse'::text);