How to encode a cs query where the search value includes a closing parenthesis

[SpiritQuaddicted]

I want to search a JSON list for the string foo=() and cannot figure it out.

curl --globoff 'http://example.com/table?select=*&and=(column->jsonlist.cs.["foo=()"]'

results in

{"code":"22P02","details":"Token \"\"foo=(\" is invalid.","hint":null,"message":"invalid input syntax for type json"}

https://postgrest.org/en/v12/references/api/url_grammar.html#reserved-characters says that if () characters are involved, I should surround something in %22 characters. But I can't figure out what exactly.

curl --globoff 'http://example.com/table?select=*&and=(column->["%22foo=()%22"])'
gives me
{"code":"22P02","details":"Token \"foo\" is invalid.","hint":null,"message":"invalid input syntax for type json"}

curl --globoff 'http://example.com/table?select=*&and=(column->%jsonlist.cs.["foo=()"]%22)'
gives me
{"code":"PGRST100","details":"unexpected \"f\" expecting \"->>\", \"->\" or delimiter (.)","hint":null,"message":"\"failed to parse logic tree ((column->\"jsonlist.cs.[\"foo=()\"]\"))\" (line 1, column 26)"}

curl --globoff 'http://example.com/table?select=*&and=(%column->jsonlist.cs.["foo=()"]%22)'
gives me
{"code":"PGRST100","details":"unexpected \"f\" expecting \"->>\", \"->\" or delimiter (.)","hint":null,"message":"\"failed to parse logic tree ((\"column->jsonlist.cs.[\"foo=()\"]\"))\" (line 1, column 26)"}

[laurenceisla]

It looks like the parser is taking the ) from "foo=()" as the end of the and operator, hence the error: Token \"\"foo=(\" is invalid.

https://postgrest.org/en/v12/references/api/url_grammar.html#reserved-characters says that if () characters are involved, I should surround something in %22 characters.

Yes, it means that the filter values should be surrounded in double quotation characters ( "" or %22%22 when encoded). In the case of the cs operator, the filter value is everything that comes after that operator, e.g. for col=cs.{a,b,c}, then the value that should be wrapped in quotations is: {a,b,c} (the whole array).

So, in your example, you should replace this:

and=(column->jsonlist.cs.["foo=()"]

With this:

and=(column->jsonlist.cs."[\"foo=()\"]"

Notice the need to escape the quotes \" for the items of the JSON array.

---

As a side-note, you don't need to use the and operator here, since &and=(col.cs.[]) is the same as doing &col=cs.[].