SQLite - JSON

About

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

Interface / functions

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

Example with json_extract

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

Management

How to know if the extension is available ?

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';

Download

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.


Powered by ComboStrap