PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL JSON: test if a value is contained in an array

I have a table with a JSONB row which holds data in this form:

id data
1 {"key_1": "value1"}
2 {"another_key": "some_value"}

Given another JSON structure like this {"key_1": ["value1", "value2"]}, what shall I use to get the first row to match a query?

I had no luck using the JSON operators of PostgreSQL in the doc https://www.postgresql.org/docs/9.5/functions-json.html

Tags:

2

Answers


  1. 0
    1. Unless you’re really using long unsupported PostgreSQL 9.5, refer to a more recent version of the documentation. If you’re really on 9.5, consider an update.
    2. If first row to match a query means you want the first by id, then add order by id limit 1 at the end.
    3. Version 12+ has JSONPath @@ predicate check and @? path exists operators. If you’re looking for a row with a jsonb value resembling the one you showed (key_1 with an array under it): demo
    create table my_table(id,jsonb_column) as values
  (1::int, '{"key_1": "value1"}'::jsonb     )
, (2     , '{"another_key": "some_value"}'  )
, (3     , '{"key_1": ["value1", "value2"]}') ;

select * from my_table 
where jsonb_column @@ '$.key_1.type()=="array"' 
order by id limit 1;
    id jsonb_column
    3 {"key_1": ["value1", "value2"]} 
    select * from my_table where jsonb_column @? '$.key_1[1]' order by id limit 1;
    id jsonb_column
    3 {"key_1": ["value1", "value2"]}
    1. Between versions 9.4.0 and 11.22 you can use @> object containment operator instead: demo
    select * from my_table where jsonb_column @> '{"key_1":[]}' order by id limit 1;

    1. If you’re looking for rows where there’s key_1 on the top level, and under it, there’s any of the values in your array, @Serg’s example got it right. In version 14 and above, you can use [key] subscript

      select t1.*
from my_table t1
inner join jsonb_each(jsonb '{"key_1":["value1","value2"]}') as find(key,val)
  on find.val @> t1.jsonb_column[find.key]
order by id limit 1;

      In earlier versions, you need the -> accessor:

      select t1.*
from my_table t1
inner join jsonb_each(jsonb '{"key_1":["value1","value2"]}') as find(key,val)
  on find.val @> (t1.jsonb_column -> find.key)
order by id limit 1;
    Login or Signup to reply.
  2. 0

    You can use <@ comparison operator

    with tbl(id, dat) as (
  select 1, '{"key_1": "value1"}'::jsonb union all
  select 2, '{"key_1": "valueX"}'::jsonb union all
  select 3, '{"key_X": "value1"}'::jsonb union all
  select 4, '{"key_1": "value2"}'::jsonb

),
prmkv as (
  select *
  from jsonb_each('{"key_1": ["value1", "value2"]}'::jsonb) 
)
select tbl.*
from tbl
join prmkv on (tbl.dat -> prmkv.key) <@ prmkv.value
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search