CODEWITHSUNDEEP
postgresqlpostgresql-9.3
Kevin Burke
Kevin Burke

Reputation: 64844

Test for item membership in Postgres json array

Let's say I have a table in Postgres that looks like this - note the zips field is json.

cities
name (text)    | zips (json)
San Francisco  | [94100, 94101, ...]
Washington DC  | [20000, 20001, ...]

Now I want to do something like select * from cities where zip=94101, in other words, testing membership.

I tried using WHERE zips ? '94101' and got operator does not exist: json ? unknown.

I tried using WHERE zips->'94101' but was not sure what to put there, as Postgres complained argument of WHERE must by type boolean, not type json.

What do I want here? How would I solve this for 9.3 and 9.4?

edit Yes, I know I should be using the native array type... the database adapter we are using doesn't support this.

Upvotes: 1

Views: 752

Answers (3)

pozs
pozs

Reputation: 36244

For PostgreSQL 9.4+, you should use json[b]_array_elements_text():
(the containment operator ? does something similar, but for a JSON array, it can only find exact matches, which could only occur, if your array contains strings, not numbers)

create table cities (
  city text,
  zips jsonb
);

insert into cities (city, zips) values
    ('Test1', '[123, 234]'),
    ('Test2', '[234, 345]'),
    ('Test3', '[345, 456]'),
    ('Test4', '[456, 123]'),
    ('Test5', '["123", "note the quotes!"]'),
    ('Test6', '"123"'), -- this is a string in json(b)
    ('Test7', '{"123": "this is an object, not an array!"}');

-- select * from cities where zips ? '123';
-- would yield 'Test5', 'Test6' & 'Test7', but none of you want

-- this is a safe solution:
select cities.*
from   cities
join   jsonb_array_elements_text(
         case jsonb_typeof(zips)
           when 'array' then zips
           else '[]'
         end
       ) zip on zip = '123';

-- but you can use this simplified query, if you are sure,
-- your "zips" column only contains JSON arrays:
select cities.*
from   cities
join   jsonb_array_elements_text(zips) zip on zip = '123';

For 9.3, you can use json_array_elements() (& convert zips manually to text):

select cities.*
from   cities
join   json_array_elements(zips) zip on zip::text = '123';

Note: for 9.3, you can't make your query safe (at least easily), you need to store only JSON arrays in the zips column. Also, the query above won't find any string matches, your array elements need to be numbers.

Note 2: for 9.4+ you can use the safe solution with json too (not just with jsonb, but you must call json_typeof(zips) instead of jsonb_typeof(zips)).

Edit: actually, the @> operator is better in PostgreSQL 9.4+, as @Ainar-G mentioned (because it's indexable). A little side-note: it only finds rows, if your column and query both use JSON numbers (or JSON strings, but not mixed).

Upvotes: 2

Ainar-G
Ainar-G

Reputation: 36199

In PostgreSQL 9.4+, you can use @> operator with jsonb type:

create table test (city text, zips jsonb);
insert into test values ('A', '[1, 2, 3]'), ('B', '[4, 5, 6]');
select * from test where zips @> '[1]';

An additional advantage of such approach is 9.4's new GIN indexes that speed up such queries on big tables.

Upvotes: 4

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95582

For 9.3, you can use json_array_elements(). I can't test with jsonb in version 9.4 right now.

create table test (
  city varchar(35) primary key,
  zips json not null
);

insert into test values
('San Francisco', '[94101, 94102]');

select * 
from (
  select *, json_array_elements(zips)::text as zip from test
) x 
where zip = '94101';

Upvotes: 1

Related Questions