SQLite - JSON
Table of Contents
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.