How to check if a value exists in a JSONB list

Question

I am trying to figure out in PostgreSQL 11 JSONB query

SELECT id, my_json_field #>> '{field_depth_1, field_depth_2}' 
FROM my_data 
WHERE my_json_field @> '{"other_field": 3}'::jsonb

If other_field is a key-value pair, this works perfectly and I get every row with other_field = 3. However, if other_field is a list of values, eg: [2,3,6,8,10], and I want to find out for every row whether the value 3 exists in the list represented by other_field, how should I write the query?

klin · Accepted Answer · 2019-10-07 19:31:51Z

1

Use the operator @>. Per the documentation:

@> jsonb Does the left JSON value contain the right JSON path/value entries at the top level?

Example:

with my_data(id, my_json_field) as (
values
    (1, '{"field_depth_1": {"field_depth_2": "something 1"}, "other_field": 3}'::jsonb),
    (2, '{"field_depth_1": {"field_depth_2": "something 2"}, "other_field": 4}'),
    (3, '{"field_depth_1": {"field_depth_2": "something 3"}, "other_field": [2,3,6,8,10]}'),
    (4, '{"field_depth_1": {"field_depth_2": "something 4"}, "other_field": [2,4,6,8,10]}')
)

select id, my_json_field #>> '{field_depth_1, field_depth_2}' as value
from my_data 
where my_json_field->'other_field' @> '3'

 id |    value    
----+-------------
  1 | something 1
  3 | something 3
(2 rows)

answered Oct 7, 2019 at 19:31

klin

123k15 gold badges241 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to check if a value exists in a JSONB list

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related