I have a column address, type
\"state\": \"NEW YORK\",
\"street\": \"\999 PROSPECT AVENUE\"}"
SELECT * FROM Houses WHERE address -> 'city' = 'SYRACUSE'
No operator matches the given name and argument type(s). You might need to add explicit type casts.
Okay, so as mentioned in the comments above, you're trying to use a JSON operator on a postgresql array, which is why you're getting a type error. There are a few ways you can approach this, depending on what you actually want to store in that field.
For these examples, I'm going to assume the following table:
CREATE TABLE houses (id INT, address <some datatype>);
If you want it to be a postgres array of
jsonb, you have to unpack the array, which you can do like so:
WITH all_addresses AS ( SELECT id, unnest(address) as add FROM houses) SELECT * FROM houses h WHERE id IN (SELECT id FROM all_addresses WHERE add->'city' = to_jsonb('SYRACUSE'::text));
If, on the other hand, you want to use a JSON array, then the query might look like this (very similar, since you still need to unnest the array):
WITH all_addresses AS ( SELECT id, jsonb_array_elements(address) as add FROM houses) SELECT * FROM houses h WHERE id IN (SELECT id FROM all_addresses WHERE add->'city' = to_jsonb('SYRACUSE'::text));
I can't quite tell from your question, but it's possible that you're only ever storing one address per row in that column (based on your example data and the fact that the column is named 'address', not 'addresses'). If that's the case, your query gets much simpler:
SELECT * FROM houses WHERE address->'city' = to_jsonb('SYRACUSE'::text);