Search code examples
sqlpostgresqljsonb

Use jsonb array in where clause

I have this table:

Directives Table:
user_id: 5
account_id: 1
filters: { "a": "my value", "names": ["myname", "yourname", "theirname"] } -- jsonb

I want to filter the rows from the Directives table by saying if filters->name is IN a set (array?) of elements:

ie:

SELECT * FROM directives
WHERE filters->names IN ['yourname', 'theirname']
Solution

  • What you show as pseudo-code would work like this with the jsonb ? operator:

    SELECT *
FROM   directives
WHERE  filters->'names' ? 'yourname';

    If you are actually looking for any intersection between the nested JSON array and an input array, use the jsonb ?| operator:

    SELECT *
FROM   directives
WHERE  filters->'names' ?| '{noname, yourname, hisname}';

    fiddle

    Notably, the filter-input for the latter is a Postgres array, not a JSON array.

    Either can be supported with a GIN index on the expression filters->'names'. See: