CODEWITHSUNDEEP
sqlpostgresqlpostgresql-json
Paymahn Moghadasian
Paymahn Moghadasian

Reputation: 10329

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

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

Upvotes: 3

Views: 2225

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656804

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:

Upvotes: 4

sticky bit
sticky bit

Reputation: 37472

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

Upvotes: 3

Related Questions