About
sqlite can query the json format with the JSON extension since the version 3.9.0 (2015-10-14)
You need the json_extension. The examples will not work if you don't have the JSON extension installed.
How to get a scalar value 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
How to read an array of string with json_each ?
Suppose that you have the following table where the phone column has a text affinity that has a JSON string array.
name | phone |
---|---|
foo | [ '701-6322455', '608-064422456' ] |
bar | [ '706-322455', '702-4422456' ] |
You can find all user with a phone that starts with 704- with the following query and json_each
SELECT DISTINCT user.name
FROM user, json_each(user.phone)
WHERE json_each.value LIKE '704-%';
The json_each function only walks the immediate children of the top-level array or object while json_tree traverse the tree.
How to extract the key value of an object in a set of rows?
json_each will give you the key details of the object.
select
key,
value
from
json_each('{ "key1":"value1","key2":"Value2" }')
Output:
key1,value1
key2,Value2
How to read an array of objects with json_each ?
Suppose that:
- you have a person table with:
- a name column for the name of the person
- a json column for all other properties
- where the json object has
- the property touchs
- storing an array of contacts object
- that you want to extract as a table
Example of Json for a person that got two contacts
{
"touchs": [
{
"date": "2022-08-18",
"description": "Lorem Ipsum",
"status": "done"
},
{
"date": "2022-09-03",
"description": "Concert !"
}
]
}
You could do it with the following query
select
jsontouchs.name,
json_extract(jsontouchs.json, '$.date') as date,
json_extract(jsontouchs.json, '$.description') as description,
json_extract(jsontouchs.json, '$.status') as status
from (select p.name as name,
touchs.value as json
from person p,
json_each(p.json, '$.touchs') as touchs
) as jsontouchs
Output example for a person named alice:
name | date | description | status |
---|---|---|---|
alice | 2022-08-18 | Lorem Ipsum | done |
alice | 2022-09-03 | Concert ! | NULL |
How to see all JSON Functions ?
All JSON function can be browsed in the JSON sqlite page.
You can extract, replace, remove and many more JSON operation.
Note that the sqlite interface (list of function) is modeled after MySQL JSON functions
Configuration
Json Extension
You need the JSON extension to get the Json functions.
You can check it with 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';
If you don't have any, you can download a pre-compiled package with JSON enabled.
For php specifically, the sqlite php page shows how to upgrade sqlite for a php installation.
Support
Why I get the error 'no such column'
When running a json query, you may get this kind of error:
SQL Error [1]: [SQLITE_ERROR] SQL error or missing database (no such column: user.json)
The column given in the function should be part of the query.
- This is not valid
select * from json_each(user.json);
- This is valid
select * from user, json_each(user.json);