How to read JSON data in SQLite ?

sqlite can query the json format with the JSON extension since the version 3.9.0 (2015-10-14)

./sqlite3
PRAGMA compile_options;

select * from pragma_compile_options() where compile_options = 'ENABLE_JSON1';

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.

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-%';

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:

All JSON function can be browsed in the JSON sqlite page.

You can extract, replace, remove and many more JSON operation.

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