"in" filter cannot express strings containing commas

[statusfailed]

Assume I have a relation "public.names", containing a single text column, "name".
I want to retrieve two names "x,y" and "a,b", using the in operator, so I make this request:

GET /names?name=in.x,y,a,b

This returns nothing, because "x,y" is parsed as two values. It seems there is no way to specify a string
containing a comma.

I have three ideas for solutions:

1. Define escaping rules for strings like "" means backslash, and "," means literal comma. This would break backwards compatibility (unless a header could be added)
2. Allow the separator character to be set in a HTTP header
3. Allow filters to be specified as JSON data

For 3, that could look something like this:

[
    {"column": "name", "operator": "in", "value": ["x,y", "a,b"]},
    {"column": "age",    "operator": "lt", "value": 3}
]

Personally, I feel like 3 is better - it could be a simple "filter DSL", which would have two frontends - the existing URL strings, and a JSON representation.

However, it might be nice to also have a patch for 2 simply allowing the separator for the "in" operator to be set via a header - it's much less work to implement and solves the immediate problem very quickly. I can probably implement this if needed!

Hopefully it's possible already and I wasted my time opening this issue though :-)

[ruslantalpa]

i would say in.:.a,b:x,y

[statusfailed]

That means breaking backwards compatibility- you would always have to specify the separator, because the current interpretation of that would be the strings

":.a", "b:x", "y"

Right?

[ruslantalpa]

no, both cases can still work, it's just an extension

[ruslantalpa]

or just go with csv specs ...

[statusfailed]

What I mean by breaking backwards compatibility is that you'd always have to specify the separator.
If you allow the user not to specify the separator, then it's impossible to tell if they really want to search for the string ":.a", or if they want to use : as the separator, and look for the string "a,b".

By CSV specs do you mean for the escaping/quoting rules?

[ruslantalpa]

Not 100% sure but i think there could be a function that can understand both formats at the same time.
As for csv, yes, escaping, quoting rules. What we have now is basically a csv without the quoting rules and it would be the easiest thing to implement since there are readily available parsers for csv.

[begriffs]

By CSV quoting do you mean just allowing double quotes in the query param like this?

GET /names?name=in."x,y","a,b"

Because if our parser could be made to handle that it seems like it would work fine. (Assuming double quotes are a valid url character, otherwise the client would have to urlencode it)

[ruslantalpa]

Yes, that is what i mean

[statusfailed]

oh that seems reasonable - that's actually the first thing I tried!