Postgres 9.5 Query matching JSON Property within Array

Question

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.

Is your column type jsonb or jsonb[]? The -> operator will work with the former, but the latter is a postgresql array and will need array operators. — jmelesky
– jmelesky, Commented Jul 21, 2016 at 17:38
Out of curiosity, why? JSON already can hold arrays, so it's not clear to me why you would want to store a postgresql array of JSON objects over a JSON array of JSON objects. — jmelesky
– jmelesky, Commented Jul 21, 2016 at 17:44
No reason. Can a JSON array of JSON objects be more easily queried? — Will
– Will, Commented Jul 21, 2016 at 17:49

jmelesky · Accepted Answer · 2016-07-21 20:06:49Z

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

Collectives™ on Stack Overflow

Postgres 9.5 Query matching JSON Property within Array

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related