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