Filtering by a value in a list in a JSONB column

Question

I have a PostgreSQL table table with a column column containing JSONB data like this:

{
 "foo": "abc",
 "bar": "123",
 "baz": [
  "a=1",
  "a=2",
  "b=5",
  "c=1"
 ],
}

I set up PostgREST.

How can I filter for records that have the string a=1 or the string b=5 but not the string c=1 in the baz array inside the JSONB column?

https://postgrest.org/en/stable/references/api/tables_views.html#json-columns is very sparse on JSON columns.

I thought it was

/table?select=id&column->>baz=cs.{"a=1","b=1"}&column->>baz=not.cs.{"c=1"}

but that gives me

{
 "code":"42883",
 "details":null,
 "hint":"No operator matches the given name and argument types. You might need to add explicit type casts.",
 "message":"operator does not exist: text @> unknown"
}

Answer 1

Apparently that was one filter too few (looks like that was a logical OR) and one angle bracket too many:

/table?select=id&column->baz=cs.{"a=1"}&column->baz=cs.{"b=1"}&column->baz=not.cs.{"c=1"}'

Answer 2

The cs operator is equivalent to @> in PostgreSQL, it will only return true if all the elements are contained in the jsonb value. You'll need to use the or operator for it to work. Also, cs should use [] instead of {}, since we are working with jsonb arrays not SQL ones.

/table?select=id&or=(column->baz.cs.["a=1"],column->baz.cs.["b=1"])&column->baz=not.cs.["c=1"]

The [] may need to be percent encoded in order to work:

table?select=id&or=(column->baz=cs.%5B"a=1"%5D,column->baz=cs.%5B"b=1"%5D)&column->baz=not.cs.%5B"c=1"%5D

As you have noticed, column->>baz=cs. didn't work because ->> returns a text value, while -> returns a jsonb one. See the Postgres JSONB operators page for extra info.