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

	json_extract(analytics, '$.words_count') as words_count


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
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.

Powered by ComboStrap