how to select postgres rows where at least one json element matches some criteria?

Question

Here's what I'm working with:

create table test(id INT, data JSON);
INSERT into test values
  (1, '[{"key": 2}, {"key": 1}]'),
  (2, '[{"key": 3}]'),
  (3, '[{"key": 1}]');


select * from test;
select id from test where 1 == ANY( json_array_elements(data) ->> 'key');

What I'm trying to do is select all rows where any of the json objects in the data column have a key key with a value of 1. I trying to extract rows 1 and 3. Note, I'm not sure if the equality comparison == right before the ANY clause is correct.

When I run the above, I get the following error: ERROR: set-returning functions are not allowed in WHERE

Answer 1

If you are free to use jsonb instead of json (which is preferable in most cases), use the jsonb "contains" operator @>:

SELECT *
FROM   test
WHERE  data  @> '[{"key": 1"}]';

Can be supported with a GIN index with default operator class or with the more specialized jsonb_path_ops:

CREATE INDEX test_data_gin_idx ON test USING gin (data jsonb_path_ops);

db<>fiddle here

Related:

• Index for finding an element in a JSON array

Answer 2

You can use EXISTS and a correlated subquery to accomplish what you want.

SELECT test.id
       FROM test
       WHERE EXISTS (SELECT *
                            FROM json_array_elements(test.data) jar(e)
                            WHERE jar.e->>'key' = '1');

SQL Fiddle