SQLite - JSON

sqlite can query the json format with the JSON extension since the version 3.9.0 (2015-10-14)

This extension gives JSON functions that permits to extract, replace, remove and many more JSON operation.

This interface (list of function) is modeled after MySQL JSON functions

If you have a json string stored in a text data type, you can extract the data via the JSON path.

With the following JSON that shows statistics about a web page in a analytics column.

{
	"chars_count": 1919,
	"fixme": 0,
	"headers_count": {
		"h1": 1,
		"h2": 3
	},
	"internal_backlinks_count": 5,
	"internal_links_count": 3,
	"words_count": 225
}

you could extract words_count with the following arguments

select
	json_extract(analytics, '$.words_count') as words_count
from
	pages

You can get the compile option

• From the pragma statement, if the output contains ENABLE_JSON1, it means that the extension is enabled

./sqlite3
PRAGMA compile_options;

• or from the pragma compile option function

select * from pragma_compile_options() where compile_options = 'ENABLE_JSON1';

The download page contains pre-compiled package with JSON enabled.

See this page for an example on how to upgrade sqlite for a php installation.