Filtering by values in a JSONB list and and/or conditions

[SpiritQuaddicted]

I am stuck with some basic understanding and would appreciate some pointers.

• Via Is there a way to filter by the content of JSON lists? #1862 I found out how to query for values in a JSON list.
• https://postgrest.org/en/latest/references/api/tables_views.html#logical-operators and How to combine multiple OR conditions #3049 have examples for and and or queries.
• I also found `or()` and `and()` operators do not work with subqueries #2014 but I do not understand most of it :(

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

{
    "tags": [
        "a=123",
        "b=hi",
        "c=test",
        "foo=alice",
        "bar:baz=bob"
    ],
    "qwe": "asd"
}

I would like to get all rows where "a=123" and "c=test" exist in the tags list with the JSONB column metadata AND where the tags list contains "b=hi" OR "postgrest=rocks" OR "idontknow=42".

The first condition was easy: table?select=*&metadata->tags=cs.["a=123","c=test"]

But I cannot figure out how to add the other rules. I thought I would just use &or=(...,...,...) but

table?select=*&metadata->tags=cs.["a=123","c=test"]&or=(metadata->tags=cs.["b=hi"],metadata->tags=cs.["postgrest=rocks"],metadata->tags=cs.["idontknow=42"])'

gives me

{"code":"PGRST100","details":"unexpected \"[\" expecting \"not\" or operator (eq, gt, ...)","hint":null,"message":"\"failed to parse logic tree ((metadata->tags=cs.[\"b=hi\"],metadata->tags=cs.[\"postgrest=rocks\"],metadata->tags=cs.[\"idontknow=42\"]))\" (line 1, column XXX)"}

(XXX is a number, I replaced the values for this example in comparison to my real data so I do not know the number that would occur here.)

[laurenceisla]

When using the or operator, the comparisons inside should not use =, but a dot . instead. For instance, this:

or=(metadata->tags=cs.["b=hi"],...)

should be:

or=(metadata->tags.cs.["b=hi"],...)

Check if it works that way.

[SpiritQuaddicted]

Thank you, that works like a charm.

Could I have found out about this in the documentation somewhere? :)

[laurenceisla]

Thank you, that works like a charm.

Great!

Could I have found out about this in the documentation somewhere? :)

Although it doesn't explicitly mention it, there is an example in the logical operators section with the correct usage.