How to read JSON data in SQLite ?

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 example will not work if you don't have the json extension installed.

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.

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

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

Powered by ComboStrap