Postgresql – Match elements of a JSON array

arrayjsonpostgresqlselect

This is a sample table :

create table test(
    id int,
    json_key json[]
);

insert into test (id,json_key) values
    (1,array['{"key1":"123"}'::json,'{"key2":"456"}'::json]),
    (2,array['{"key1":"789"}'::json,'{"key2":"456"}'::json]);

json_key output is :

{{"key1":"123"},{"key2":"456"}}

I am trying to execute

select * from test 
where json_key->>'key2'='456';

but it does not work 🙁
how can I run this query?

Best Answer

One way of trying to match something against array elements is using ANY. The only thing is to find the right operator that can be used with it. In this case, <@ will be our friend (you can find it and others at the right page of the docs). When that's done, it becomes easy:

SELECT * FROM test WHERE '{"key2": "456"}'::jsonb <@ ANY(json_key::jsonb[]);

 id │                  json_key                   
────┼─────────────────────────────────────────────
  1 │ {"{\"key1\":\"123\"}","{\"key2\":\"456\"}"}
  2 │ {"{\"key1\":\"789\"}","{\"key2\":\"456\"}"}

To prove it really does what we expect:

test=# SELECT * FROM test WHERE '{"key1": "456"}'::jsonb <@ ANY(json_key::jsonb[]);

 id │ json_key 
────┼──────────

test=# SELECT * FROM test WHERE '{"key1": "123"}'::jsonb <@ ANY(json_key::jsonb[]);

 id │                  json_key                   
────┼─────────────────────────────────────────────
  1 │ {"{\"key1\":\"123\"}","{\"key2\":\"456\"}"}

Watch it in action on dbfiddle.