Will Will - 4 months ago 9
JSON Question

Postgres 9.5 Query matching JSON Property within Array

I have a column address, type

jsonb[]
, within table Houses which contains an array looking something like this:

"{
"{\"zip\": \"13203-1807\",
\"city\": \"\SYRACUSE\",
\"state\": \"NEW YORK\",
\"street\": \"\999 PROSPECT AVENUE\"}"
}"


I am trying to query all Houses where the Address City is "Syracuse"; so far I have:

SELECT * FROM Houses WHERE address -> 'city' = 'SYRACUSE'


And receive this error:

No operator matches the given name and argument type(s). You might need to add explicit type casts.


I have looked through PG Official Documentation on JSON Functions and Operators as well as several StackOverflow answers to no avail.

Answer

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);
Comments