Re: jsonb problematic operators

On Fri, Dec 9, 2016 at 4:50 AM, Jordan Gigov <coladict(at)gmail(dot)com> wrote:

> There is this problem with the jsonb operators "? text" "?| text[]"
> and "?& text[]" that the question mark is typically used for prepared
> statement parameters in the most used abstraction APIs in Java and
> PHP.
>

​Unfortunately true. These APIs made a poor decision in taking a very
useful query operator character, the question mark, and turning it into a
position-important placeholder for query parameters. Using "$1, $2, $3,
etc..." is a much better design since you have fewer things to worry about
if you modify the query and add (or want to reuse) a parameter.

Given that PostgreSQL already choose to go with the better designed API
here the fact that the relatively new JSON feature allows "?" to be used as
an operator character makes perfect sense.

>
> This really needs an alternative. Something like "HAS text", "HAS
> ANY(text[])" and "HAS ALL(text[])" same as regular array usage. It
> probably should be another word that has less chance of becoming a
> conflict with another operator in future SQL specifications, but
> that's for you to decide.
>

​I don't think making it a keyword is a good idea, or possible via
extension, but otherwise you are free to create custom operators (and
related infrastructure) if this bothers you enough. Such a "JDBC
compatability extension" would probably be welcomed by the community.​

> It's not a good idea to expect everyone else to make for workarounds
> for problems you choose to create.
>

The choosing was for a superior, and internally consistent, design. While
I see the problem you bring up I don't see introducing yet another set of
alternative operators to core. But as mentioned one of the advantages of
PostgreSQL is its extensability and hopefully someone will enjoy working
with Open Source PostgreSQL in their affected language enough to consider
doing something about it.

David J.