Filtering by values in a JSONB list with pattern matching operators?

[SpiritQuaddicted]

My table has a field called metadata which is JSONB in a format like this:

{
    "tags": [
        "a=123",
        "b=2023-01-01",
        "b=2024-01-01",
        "b=2024-02-01",
        "c=test",
        "c=somethingelse",
        "c=cagain",
        "foo=alice",
        "bar:baz=bob"
    ],
    "qwe": "asd"
}

Is it possible to query this array with the pattern matching operators (like, ilike, match, imatch)?

E.g. I would like to get all results that have any entries matching b=2024.* or ones that have any c=.* entries.

[laurenceisla]

I don't think that's possible to do directly. This looks like a case for JSON Path, which is not provided by PostgREST out of the box. You need to use a FUNCTION or VIEW to get a filter like this:

select *
from tbl
where metadata @@ '$.tags[*] ? (@ starts with "b=2024" || @ starts with "c=.")';