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.
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
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.
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
Suppose that:
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 |
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
You need the JSON extension to get the Json functions.
You can check it with the compile option
./sqlite3
PRAGMA compile_options;
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.
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.
select * from json_each(user.json);
select * from user, json_each(user.json);