CODEWITHSUNDEEP
jsonpostgresqljsonb
Harish
Harish

Reputation: 161

PostgreSQL searching value in inside JSON array

I want to search element inside JSONB in PostgreSQL here is my JSON

CREATE TABLE test
AS
  SELECT jsondata::jsonb
  FROM ( VALUES
    ( '{"key1": 1, "keyset": [10, 20, 30]}' ),
    ( '{"key1": 1, "keyset": [10, 20]}' ),
    ( '{"key1": 1, "keyset": [30]}' ),
    ( '{"key1": 1 }' ),
    ( '{"key1": 1, "key2": 1}' )
  ) AS t(jsondata);

in above table keyset not exist in all rows and my query is 

SELECT * FROM test WHERE  jsondata->>'keyset' = 10;

above query is giving empty result, and expected output is

jsondata
------------------------------------
{"key1": 1, "keyset": [10, 20, 30]}
{"key1": 1, "keyset": [10, 20]}

Upvotes: 5

Views: 9863

Answers (2)

Evan Carroll
Evan Carroll

Reputation: 1

What you want is this

SELECT jsondata @> '{"keyset": [10]}' FROM foo;

So it looks like this

 SELECT jsondata, jsondata @> '{"keyset": [10]}' FROM foo;
              jsondata               | ?column? 
-------------------------------------+----------
 {"key1": 1, "keyset": [10, 20, 30]} | t
 {"key1": 1, "keyset": [10, 20]}     | t
 {"key1": 1, "keyset": [30]}         | f
 {"key1": 1}                         | f
 {"key1": 1, "key2": 1}              | f

the @> operator checks for containment in PostgreSQL. I put in the select to show you the evaluations..

SELECT jsondata
FROM foo
WHERE jsondata @> '{"keyset": [10]}';

Upvotes: 7

Laurenz Albe
Laurenz Albe

Reputation: 247865

SELECT jsondata
FROM test
   JOIN LATERAL jsonb_array_elements_text(jsondata->'keyset') a(v)
      ON TRUE
WHERE a.v::integer = 10;

Upvotes: 1

Related Questions