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

Are you bound to the data type `json` or is `jsonb` an option? Is the table big and performance relevant? Please *always* declare your version of Postgres. — Erwin Brandstetter, Aug 03 '18 at 20:20
Please refer to this similar question on why ANY doesn't work - https://stackoverflow.com/questions/21675174/querying-data-within-json-array-data-field/21677486#21677486 — rs., Aug 03 '18 at 20:30
in SQL the comparison operator is `=` fix that and it should work — Jasen, Aug 04 '18 at 09:51

score 4 · Accepted Answer · answered Aug 03 '18 at 20:26

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

Index for finding an element in a JSON array

score 3 · Answer 2 · answered Aug 03 '18 at 20:16

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

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

2 Answers2