Couple of points:
1. PG and MySQL appear to be fundamentally incompatible in their JSON
functions. You cannot (as far as I can tell) write SQL using JSON
that works on both PG and MySQL.
<p>
My objective is to make it so you can write SQL that works on both
SQLite and PG or that works on both SQLite and MySQL. But in as much
as PG and MySQL are incompatible, it is not possible to write SQL that
will work on all three.
2. The ->> operator is the one that works the same on SQLite as it does
on PG and MySQL. You could perhaps make the argument that I should use
something other than -> for compatibility. But that argument breaks
down when you try to use it for ->>, because ->> *is* compatible between
PG/MySQL and SQLite. By "compatible" I mean that ->> uses in PG or
MySQL should also work in SQLite. It is not true the other way around.
Because SQLite's ->> is a union of the capabilities of ->> in PG and
MySQL, you can write ->> that works in SQLite but not in PG or not in
MySQL.
<p>
If you do try to make the argument that I should choose a different
operator other than -> in SQLite, I will push back as follows:
<ol type="a"><li>There are no two RDBMSes for which -> works the same way
today, so compatibility is not really a factor here, and
<li> The SQLite -> operator, though slightly different from PG and MySQL,
is close enough to being the same that many use cases would not notice
the difference, and
<li> The -> operator is
familiar and convenient to programmers with prior
exposure to C/C++.</ol>