Having explored this question further, I now think I better understand why
MySQL and PG have separate -> and ->> operators. It is because of their rigid
type system, that requires each function to always return values of a specific
type. Hence you have -> for returning JSON values and ->> for returning TEXT
values. SQLite is not constrained in this way, and so, in theory, SQLite can
have a single operator -> that returns either JSON or TEXT (or INTEGER or REAL)
as appropriate for the JSON and path expression. This is a simpler and more
intuitive approach, but is not possible in MySQL and PG due to the limitations
of their type system.
Hence, for the sake of compatibility, I'm proposing to completely revamp my
SQLite JSON improvement proposal as follows:
* Omit the json_nextract() and json_ntype() functions, as the same thing
can be accomplished using CASE and the need for these does not seem to
come up as often as I anticipated.
* Merge the JSON functions into the SQLite core. This is mostly transparent
to applications. It just means the JSON functions are included by default,
and they use a little less memory.
* Add a -> operator that works kind of like json_extract() but that always
returns JSON. Thus -> is compatible with MySQL and PG.
* Add a ->> operator that always returns a primitive SQL type - TEXT,
INTEGER, REAL, or NULL - depending on the input JSON and the path
expression. This behavior is slightly different from MySQL and PG
in that in those others, ->> always returns either TEXT or a NULL,
and never INTEGER or REAL. But I haven't been able to come up with
a situation where returning INTEGER or REAL when the underlying JSON
value is INTEGER or REAL would cause a problem, so I'm going to call
this new ->> operator "compatible" too.
My intent is to write up the new proposal in more detail, with examples,
and add a working implementation to a branch (probably the
[json-in-core][2] branch) at some point. But I have some other unrelated
matters that will need to take priority so I'm not sure how soon that will
happen.
[2]: src:/timeline?r=json-in-core