Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE menus (menu_id int, dishes jsonb);
INSERT INTO menus VALUES
  (1, '{
    "desserts": [
        {"id": 1, "name": "chocolate cake"},
        {"id": 2, "name": "banana split"}
    ],
    "appetizers": [
        {"id": 3, "name": "nachos"},
        {"id": 4, "name": "mozzarella sticks"},
        {"id": 5, "name": "buffalo wings"}
    ]}')
, (2, '{
    "main": [
        {"id": 6, "name": "duck"}
    ]}')  -- added for demo  (match)
, (3, '{
    "desserts": [
        {"id": 66, "name": "duck"}
    ]}')  -- added for demo (no match)
;

CREATE TABLE

INSERT 0 3

-- applicable index (!)
CREATE INDEX foo ON menus USING gin (dishes jsonb_path_ops);

CREATE INDEX

-- which IDs are referenced by at least one row in menus.dishes - only appetizers!
SELECT *
FROM   unnest ('{3,5,7,6}'::int[]) i(id)
WHERE  EXISTS (
   SELECT FROM menus
   WHERE  dishes @? format('$.appetizers[*].id ? (@ == %s)', i.id)::jsonpath
   );

id

3

5

SELECT 2

-- which IDs are referenced by at least one row in menus.dishes
SELECT *
FROM   unnest ('{3,5,7,6}'::int[]) i(id)
WHERE  EXISTS (
   SELECT FROM menus
   WHERE  dishes @? format('$.*[*].id ? (@ == %s)', i.id)::jsonpath
   );

id

3

5

6

SELECT 3

-- manual solution
SELECT *
FROM   menus
WHERE  dishes @? '$.*[*].id ? (@ == 3 || @ == 5 || @ == 7 || @ == 6)';

menu_id
dishes

1
{"desserts": [{"id": 1, "name": "chocolate cake"}, {"id": 2, "name": "banana split"}], "appetizers": [{"id": 3, "name": "nachos"}, {"id": 4, "name": "mozzarella sticks"}, {"id": 5, "name": "buffalo wings"}]}

2
{"main": [{"id": 6, "name": "duck"}]}

SELECT 2

-- demo
SELECT menu_id, jsonb_path_query_array(dishes, '$.*[*].id ? (@ == 3 || @ == 5 || @ == 7 || @ == 6)')
FROM   menus;

menu_id
jsonb_path_query_array

1
[3, 5]

2
[6]

3
[]

SELECT 3

-- index usable?
SET enable_seqscan = off;

SET

-- YES
EXPLAIN
SELECT *
FROM   unnest ('{3,5,7,6}'::int[]) i(id)
WHERE  EXISTS (
   SELECT FROM menus
   WHERE  dishes @? format('$.*[*].id ? (@ == %s)', i.id)::jsonpath
   );

QUERY PLAN

Nested Loop Semi Join  (cost=2.53..28.25 rows=1 width=4)

  ->  Function Scan on unnest i  (cost=0.00..0.04 rows=4 width=4)

  ->  Bitmap Heap Scan on menus  (cost=2.53..6.55 rows=1 width=32)

        Recheck Cond: (dishes @? (format('$.*[*].id ? (@ == %s)'::text, i.id))::jsonpath)

        ->  Bitmap Index Scan on foo  (cost=0.00..2.53 rows=1 width=0)

              Index Cond: (dishes @? (format('$.*[*].id ? (@ == %s)'::text, i.id))::jsonpath)

EXPLAIN

-- YES
EXPLAIN
SELECT *
FROM   menus
WHERE  dishes @? '$.*[*].id ? (@ == 3 || @ == 5 || @ == 7 || @ == 6)';

QUERY PLAN

Bitmap Heap Scan on menus  (cost=65.69..69.70 rows=1 width=36)

  Recheck Cond: (dishes @? '$.*[*]."id"?(((@ == 3 || @ == 5) || @ == 7) || @ == 6)'::jsonpath)

  ->  Bitmap Index Scan on foo  (cost=0.00..65.69 rows=1 width=0)

        Index Cond: (dishes @? '$.*[*]."id"?(((@ == 3 || @ == 5) || @ == 7) || @ == 6)'::jsonpath)

EXPLAIN

-- NO
EXPLAIN
SELECT *
FROM   unnest ('{3,5,7}'::int[]) i(id)
WHERE  EXISTS (
   SELECT FROM menus
   WHERE  jsonb_path_exists(dishes, '$.*[*].id ? (@ == $id)', jsonb_build_object('id', id))
   );

QUERY PLAN

Nested Loop Semi Join  (cost=10000000000.00..10000000001.20 rows=1 width=4)

  Join Filter: jsonb_path_exists(menus.dishes, '$.*[*]."id"?(@ == $"id")'::jsonpath, jsonb_build_object('id', i.id), false)

  ->  Function Scan on unnest i  (cost=0.00..0.03 rows=3 width=4)

  ->  Materialize  (cost=10000000000.00..10000000001.05 rows=3 width=32)

        ->  Seq Scan on menus  (cost=10000000000.00..10000000001.03 rows=3 width=32)

JIT:

  Functions: 8

  Options: Inlining true, Optimization true, Expressions true, Deforming true

EXPLAIN
menu_id	dishes
1	{"desserts": [{"id": 1, "name": "chocolate cake"}, {"id": 2, "name": "banana split"}], "appetizers": [{"id": 3, "name": "nachos"}, {"id": 4, "name": "mozzarella sticks"}, {"id": 5, "name": "buffalo wings"}]}
2	{"main": [{"id": 6, "name": "duck"}]}