how to select all records containing certain values from a postgres json field containig an array

Question

I have tableA containing a json field data. abstracted examples for field data:

{"sequence": [123,456,789]}
{"sequence": [456,789]}
{"sequence": [789, 12]}

update: added sqlfiddle with some sample data -> http://sqlfiddle.com/#!17/62475/24

with

select * from tableA where (data->'sequence') @> '[456]';

I am able to select all records containing 456:

{"sequence": [123,456,789]}
{"sequence": [456,789]}

I am struggling to select all records containing 123 or 456. is this possible?

it would also be useful to include 123 or 456 as a subquery like:

select * from tableA where (data->'sequence') in (select data_id from tableB);

Haleemur Ali · Accepted Answer · 2019-08-20 07:05:32Z

1

Use ANY to to test if the jsonb array contains any of the right values, which can be an array or a subquery, using your sqlfiddle example

SELECT *
FROM tableA
WHERE (data->'sequence') @> ANY(SELECT (data_id::TEXT)::JSONB FROM tableB)

You can also pass an array literal, in this case it would require an array of JSONB values, i.e. the right side of @> could be replaced with the literal ANY('{123,456}'::JSONB[])

Alternatively, Use the && to test for array overlap. It is first necessary to convert the JSON/JSONB array to a native array

SELECT tableA.*
FROM tableA 
JOIN LATERAL (
  SELECT ARRAY_AGG(v::INT) y 
  FROM JSONB_ARRAY_ELEMENTS_TEXT(data->'sequence') v
) x ON TRUE
WHERE x.y && '{123, 456}'

You can also replace the array literal '{123, 456}' with a subquery that returns an array of integers, such as (SELECT ARRAY_AGG(data_id) FROM tableB)

Another option would be to use or in your where clause

select *
from tableA 
where (data->'sequence') @> '[456]'
   or (data->'sequence') @> '[123]'

edited Aug 20, 2019 at 7:05

answered Aug 19, 2019 at 22:11

Haleemur Ali

28.6k6 gold badges67 silver badges89 bronze badges

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

6 Comments

udo Over a year ago

thanks ali. yes, of course but too "manual". actually I have a case wher I have to check for 43 different ids. unfortunately adding them manually as you suggested is not an option :| I added a sqlfiddle (sqlfiddle.com/#!17/62475/24). how would you extend this with any?

Haleemur Ali Over a year ago

see updated answer, don't know why I didn't immediately think of this solution

user330315 Over a year ago

Unfortunately the && operator only works with native arrays, not with JSON arrays.

udo Over a year ago

yups -> operator does not exist. the any suggestion is probably the way to go so the values to check against can be retrieved by a subquery...

Haleemur Ali Over a year ago

@a_horse_with_no_name, thanks for catching that. You could always expand & array_agg to obtain a native array which does support &&.

|

Collectives™ on Stack Overflow

how to select all records containing certain values from a postgres json field containig an array

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related