/ JSON Functions Enhancements (2022)

This document summaries enhancements to the SQLite JSON support added in early 2022.

1.0 Change summary:

  1. New -> and ->> operators that work like MySQL and PostgreSQL (PG).
  2. JSON functions are built-in rather than being an extension. They are included by default, but can be omitted using the -DSQLITE_OMIT_JSON compile-time option.

2.0 New operators -> and ->>

The SQLite language adds two new binary operators -> and ->>. Both operators are similar to json_extract(). The left operand is JSON and the right operand is a JSON path expression (possibly abbreviated for compatibility with PG - see below). So they are similar to a two-argument call to json_extract().

The difference between -> and ->> (and json_extract()) is as follows:

In MySQL and PG, the ->> operator always returns TEXT (or NULL) and never INTEGER or REAL. This is due to limitations in the type handling capabilities of those systems. In MySQL and PG, the result type a function or operator may only depend on the type of its arguments, never the value of its arguments. But the underlying JSON type depends on the value of the JSON path expression, not the type of the JSON path expression (which is always TEXT). Hence, the result type of ->> in MySQL and PG is unable to vary according to the type of the JSON value being extracted.

The type system in SQLite is more general. Functions in SQLite are able to return different datatypes depending on the value of their arguments. So the ->> operator in SQLite is able to return TEXT, INTEGER, REAL, or NULL depending on the JSON type of the value being extracted. This means that the behavior of the ->> is slightly different in SQLite versus MySQL and PG in that it will sometimes return INTEGER and REAL values, depending on its inputs. It is possible to implement the ->> operator in SQLite so that it always operates exactly like MySQL and PG and always returns TEXT or NULL, but I have been unable to think of any situations where returning the actual JSON value this would cause problems, so I'm including the enhanced functionality in SQLite.

The table below attempts to summarize the differences between the -> and ->> operators and the json_extract() function, for SQLite, MySQL, and PG. JSON values are shown using their SQL text representation but in a bold font.

JSONPATH-> operator
->> operator
->> operator
'{"a":123}' '$.a' '123' '123' 123 123
'{"a":4.5}' '$.a' '4.5' '4.5' 4.5 4.5
'{"a":"xyz"}' '$.a' '"xyz"' 'xyz' 'xyz' 'xyz'
'{"a":null}' '$.a' 'null' NULL NULL NULL
'{"a":[6,7,8]}' '$.a' '[6,7,8]' '[6,7,8]' '[6,7,8]' '[6,7,8]'
'{"a":{"x":9}}' '$.a' '{"x":9}' '{"x":9}' '{"x":9}' '{"x":9}'
'{"b":999}' '$.a' NULL NULL NULL NULL

Important points about the table above:

2.1 Abbreviated JSON path expressions for PG compatibility

The table above always shows the full JSON path expression: '$.a'. But PG does not accept this syntax. PG only allows a single JSON object label name or a single integer array index. In order to provide compatibility with PG, The -> and ->> operators in SQLite are extended to also support a JSON object label or an integer array index for the right-hand side operand, in addition to a full JSON path expression.

Thus, a -> or ->> operator that works on MySQL will work in SQLite. And a -> or ->> operator that works in PG will work in SQLite. But because SQLite supports the union of the disjoint capabilities of MySQL and PG, there will always be -> and ->> operators that work in SQLite that do not work in one of MySQL and PG. This is an unavoidable consequence of the different syntax for -> and ->> in MySQL and PG.

In the following table, assume that "value1" is a JSON object and "value2" is a JSON array.

SQL expression Works in MySQL?Works in PG?Works in SQLite
value1->'$.a' yes no yes
value1->'a' no yes yes
value2->'$[2]' yes no yes
value2->2 no yes yes

The abbreviated JSON path expressions only work for the -> and ->> operators in SQLite. The json_extract() function, and all other built-in SQLite JSON functions, continue to require complete JSON path expressions for their PATH arguments.

3.0 JSON moved into the core

The JSON interface is now moved into the SQLite core.

When originally written in 2015, the JSON functions were an extension that could be optionally included at compile-time, or loaded at run-time. The implementation was in a source file named ext/misc/json1.c in the source tree. JSON functions were only compiled in if the -DSQLITE_ENABLE_JSON1 compile-time option was used.

After these enhancements, the JSON functions are now built-ins. The source file that implements the JSON functions is moved to src/json.c. No special compile-time options are needed to load JSON into the build. Instead, there is a new -DSQLITE_OMIT_JSON compile-time option to leave them out.