PostgreSQL searching value in inside JSON array

Question

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]}

Evan Carroll · Accepted Answer · 2017-04-15 19:04:50Z

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]}';

Laurenz Albe · Accepted Answer · 2017-04-14 12:08:25Z

1

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

answered Apr 14, 2017 at 12:08

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Collectives™ on Stack Overflow

PostgreSQL searching value in inside JSON array

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related