Sqlite Banner


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

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

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

  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.

  json_each('{ "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

       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


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


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

Discover More
Card Puncher Data Processing
How to upgrade SQLite to the latest shared library version

This howto shows you how to upgrade your shared SQLite library
Sqlite Banner
Sqlite - (Compile) Options

Options in Sqlite permits to see which feature is enabled or not. The options are dependent: of the compile option. They may be or not enabled when creating the sqlite application on the sqlite...

Share this page:
Follow us:
Task Runner