N
N
NikolayAlb2019-07-09 16:36:55
PostgreSQL
NikolayAlb, 2019-07-09 16:36:55

Is it possible to filter by jsonb array (similar to where in)?

Good afternoon. Records in the database are stored with the jsonb field categories in this form:

id |   name  | categories
1  |   car   | [1, 2]
2  |   dog   | [2, 3]
3  |   cat   | [4, 5]

Is it possible to produce an analogue WHERE INfor such a field? I googled and did not find a solution, there are options when a full-fledged json is stored in the field, but they are not suitable for an array.
There is an operator @>, but it requires all provided identifiers. An analogue of the operation I need is - &&(but this operator is only suitable for standard arrays).

Answer the question

In order to leave comments, you need to log in

1 answer(s)
D
Dmitry Shitskov, 2019-07-09
@Zarom

Operator ?| doesn't fit? https://www.postgresql.org/docs/10/functions-json.html
SELECT '["1", "2"]'::jsonb ?| array['1','5','6'];

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question