2

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?

4

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.