There is a way: combine the containment operator @>
with the ANY
construct:
SELECT d
FROM grp
WHERE d->'customers' @> ANY (ARRAY ['[{"id":"1"}]', '[{"id":"5"}]']::jsonb[]);
Or:
...
WHERE d->'customers' @> ANY ('{"[{\"id\": \"1\"}]","[{\"id\": \"5\"}]"}'::jsonb[]);
It's essential to cast the array to jsonb[]
explicitly. And note that each element is a JSON array containing an object inside like the operator @>
requires. So it's an array of JSON arrays.
You can use an index for this:
The manual explicitly states that the operator ?|
is for strings matching keys or array elements only (not values):
Do any of the strings in the text array exist as top-level keys or array elements?
First off, I would consider a normalized DB design instead of the convoluted jsonb
column, which makes indexing and complex queries harder.
How to index by [array].customer_id.id
?
See my answer to your previous question:
You could have two GIN indexes like instructed there, one on tour_id
, another one on customer_id
, or a single multicolumn index, and then query:
SELECT * FROM tbl
WHERE f_extract_tour_ids(registration) @> '{d61802ff-3eec-4a72-97ca-832f51b96bf0}'::uuid[]
AND f_extract_customer_ids(registration) @> '{f492b26d-d260-4bcb-8e96-6532fdf38665}'::uuid[];
Note, this returns rows with tour_id
and customer_id
matching in any top-level object of the array, not necessarily in the same. If you want to find rows where tour_id
and customer_id
match in the same object, you have to do more ...
jsonb_path_ops
index
However, with a growing number of different queries (requiring multiple special indexes), a single generic (much bigger) jsonb_path_ops
index on the whole column becomes more competitive. Or even one with the default operator class operator class jsonb_ops
- which is bigger, yet.
CREATE INDEX foo ON tbl USING GIN (registration jsonb_path_ops);
Queries to go with it:
For rows with tour_id
and customer_id
matching in the same top-level object:
SELECT * FROM tbl
WHERE registration @> '[{"tour_id": {"id": "d61802ff-3eec-4a72-97ca-832f51b96bf0"}
, "customer_id": {"id": "f492b26d-d260-4bcb-8e96-6532fdf38665"}}]';
For rows with tour_id
and customer_id
matching in any top-level object, not necessarily the same:
SELECT * FROM tbl
WHERE registration @> '[{"tour_id": {"id": "d61802ff-3eec-4a72-97ca-832f51b96bf0"}}
, {"customer_id":{"id":"f492b26d-d260-4bcb-8e96-6532fdf38665"}}]';
This solution is slower because of the much bigger index carrying all the other noise in your jsonb
column. But it is much more versatile and simpler.
If the first solutions make your head ache, use this one.
Best Answer
You can do it like this:
This query is still complicated and not very efficient.
The reason for that is the terrible data model. JSON in the database can be useful, but just stuffing all your data into a JSON column is a certain recipe for complicated and slow queries.
You should implement this with a junction table that implements the m-to-n relationship between the tables.
My rule of thumb is that you should use JSON in the database only for columns that are not used a lot inside the database.