Test given IDs for existence in any row of a JSON column

Question

In my Postgres 13.10 database, I have a table menus with a JSON column named dishes. This column contains values in the following format:

{
    "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"}
    ]
}

The data type is json, but I do have the option of changing it to jsonb, if that would help with query performance.

Given a list of appetizer IDs (for example, 3 and 5), I need to determine which IDs are referenced by at least one row in this table.

How can I write a query to do this?

(Of course, this is a contrived example.)

Answer 1

Assuming jsonb. (Would not scale well for plain json).
To make it fast, use SQL/JSON path functionality added with Postgres 12:

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

This searches the key "id" in all arrays nested in top-level objects of menus.dishes. Or restrict to the top-level key "appetizers":

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

fiddle

Be sure to have a GIN index on menus(dishes). Ideally a jsonb_path_ops index:

CREATE INDEX menus_dishes ON menus USING gin (dishes jsonb_path_ops);

The main feature is to build a jsonpath expression off unnested IDs dynamically. This way it works for any number of input IDs, any number of top-level objects in the JSON documents, and any number of nested array items - while still using above index.

Related:

• Filter JSON values at least one key is in a given array and has a given value

Aside

Have you considered making an actual relational DB out of your JSON documents? Keyword "database normalization". Would make queries like this a whole lot simpler and faster.

Answer 2

You didn't describe how you want the list to be provided. Here I'm providing it as a VALUES construct.

You can use the @> containment test, but will need to cast the data to JSONB (or just store it that way in the first place) in order to use that. The "at least one row" suggests use of the exists construct.

select t,
  exists (select 1 from menus where dishes::jsonb @> jsonb_build_object('appetizers',jsonb_build_array(jsonb_build_object('id',t))) )
from (values (3),(5),(9)) t(t);

 t | exists
---+--------
 3 | t
 5 | t
 9 | f