Check whether array in JSONB column includes any of the values in another array

Question

Problem

Find all records in sample data where foo->bar include at least one item from given array e.g. [1,2]

Sample data

Record 1 => 'foo': {
  'bar': [1,2]
}

Record 2 => 'foo': {
  'bar': [3,4]
}

Record 3 => 'foo': {
  'bar': [5,7]
}

Record 4 => 'foo': {
  'bar': [1]
}

Record 5 => 'foo': {
  'bar': [2,3]
}

Expected result

Record 1 => 'foo': {
  'bar': [1,2]
}


Record 4 => 'foo': {
  'bar': [1]
}

Record 5 => 'foo': {
  'bar': [2,3]
}

I tried using operators @> and ?|, the first checks against JSOB and returns only if all items are included. Second has issues with types JSOB => Integer[]

SQL

SELECT  "some_table".* FROM "some_table" WHERE (foo->'bar' @> '[1,2]'::jsonb);

Rails Scope

scope :for_bar, -> (bars) { where("foo->'bar' @> ?::jsonb", bars.to_json) }

Any suggestions to solve this.

Sebastián Palma · Accepted Answer · 2022-09-07 07:01:12Z

6

I think what you need is:

extract bar from foo,
map any element from bars to JSON,
cast the bound values to array,
cast the array of bound values to jsonb[],
check if any of the left JSON values contain the right JSON path/value entries at the top level (@>):

SomeTable.where("foo->'bar' @> ANY(ARRAY[?]::JSONB[])", [1, 2].map(&:to_s))

edited Sep 7, 2022 at 7:01

answered May 24, 2020 at 9:12

Sebastián Palma

33.6k6 gold badges45 silver badges65 bronze badges

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

1 Comment

Indigo Over a year ago

Makes total sense, Thanks a ton.

sticky bit · Accepted Answer · 2020-05-24 02:16:57Z

0

One possible solution is to use jsonb_array_elements() to unroll the JSON arrays into sets. Then, in an EXISTS subquery, inner join these sets on common elements.

SELECT *
       FROM some_table
       WHERE EXISTS (SELECT *
                            FROM jsonb_array_elements(some_table.foo->'bar') t (v)
                                 INNER JOIN jsonb_array_elements('[1,2]'::jsonb) s (v)
                                            ON t.v = s.v);

db<>fiddle

answered May 24, 2020 at 2:16

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

Comments

Unkas · Accepted Answer · 2023-03-13 21:29:33Z

-2

MyTable.where("my_attr->'jsonb_field' @> ANY(ARRAY[?]::JSONB[])", ['Mary'.to_json, 'Jane'.to_json])

answered Mar 13, 2023 at 21:29

Unkas

3,7362 gold badges21 silver badges25 bronze badges

Collectives™ on Stack Overflow

Check whether array in JSONB column includes any of the values in another array

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related