SQLite 3.38.0 is the latest release of SQLite, bringing improved syntax for JSON queries, a new diagnostic interface, CLI enhancements, and more.
SQLite 3.38.0 introduces new -> and ->> operators to allow developers to define queries over JSON data. Both operators select a subcomponent of the JSON operand to their left based on the JSON path expression to their right, e.g. json_field->'$.json_component'. The difference between the two operators is -> returns a JSON value type, while ->> returns the SQL datatype extracted from a JSON value, such as text, integer, and so on.
For example,
'{"a":2,"c":[4,5,{"f":7}]}' -> '$.c[2]'
yields '{"f":7}'. Similarly,
'{"a":2,"c":[4,5,{"f":7}]}' -> '$.c[2].f'
yields '7', while
'{"a":2,"c":[4,5,{"f":7}]}' ->> '$.c[2].f'
yields the integer value 7.
This is how you can select all rows in a table with a simple JSON record stored in json_col:
select * from test_table where json_col->>'$.json_key' = 'some_value';
If you are used to json_extract, it is worth noting that it differs slightly from -> and ->> in that it combines their effects together. That means, json_extract extracts a component from a JSON value and returns it as a JSON value in case of arrays and objects, and as an SQL value for strings, numeric values, etc.
The syntax SQLite supports for JSON paths is compatible with both MySQL's and PostgreSQL's. This means SQLite will understand X (PostgreSQL style) as the same as $.X (MySQL style) if X is a text label, and n as the same as $[n].
On a related note, JSON support is on by default in version 3.38 and does not require rebuilding the library using the -DSQLITE_ENABLE_JSON1 option.
SQLite 3.38.0 also tries to ease a pain point for developers caused by its usually terse error messaging. To make it easier to identify exactly where a syntax error occurs in an SQL query, the sqlite3_error_offset() can sometimes narrow it down to a specific character.
As a final note, SQLite CLI provides now enhanced support for columnar output modes by correctly handling tabs and newlines as well as supporting options like "--wrap N", "--wordwrap on", and "--quote". Additionally, the .import command is now able to automatically disambiguate column names.
Do not miss the official release note for a complete list of all new features in SQLite 3.38.0.