SQLite Basics: Read and Update JSON Documents

An API response may contain optional settings or a list of tags that you want to inspect with SQL. SQLite’s JSON functions let you read those values without exporting every row to an application first. These examples use ordinary JSON text, not JSONB.

The functions used here require SQLite 3.9.0 or later with JSON support enabled. JSON became enabled by default in 3.38.0, but a custom build can still omit it. The installed runtime below was tested with JSON support; historical minimum versions were not run.

Prepare a scratch directory

Tested on Linux with Python 3.12.14 and SQLite 3.53.1. You need Bash, Python 3 with SQLite support, a text editor, and mktemp, rm, and rmdir. No additional Python packages are needed. Keep the same terminal open and save files as UTF-8.

start_dir="$PWD"
work_dir="$(mktemp -d)" || exit 1
cd -- "$work_dir" || exit 1

The standalone SQLite CLI was unavailable on the test machine. Save this runner as run-sql.py. Each invocation uses a fresh in-memory database and prints pipe-separated rows, displaying missing values as NULL. The toy database disappears when the connection closes. Put each statement’s ending semicolon at the end of a line, as shown; do not put two complete statements on one line. This is a teaching runner, not a general SQL shell or CSV exporter.

from pathlib import Path
import sqlite3
import sys

connection = sqlite3.connect(":memory:", isolation_level=None)
try:
    statement = ""
    for line in Path(sys.argv[1]).read_text(encoding="utf-8").splitlines(True):
        statement += line
        if sqlite3.complete_statement(statement):
            cursor = connection.execute(statement)
            for row in cursor:
                print("|".join("NULL" if value is None else str(value) for value in row))
            statement = ""
    if statement.strip():
        raise ValueError("Unfinished SQL statement")
finally:
    connection.close()

1. Read fields and distinguish missing values

Save as read-json-fields.sql:

CREATE TABLE parcels (
    id INTEGER PRIMARY KEY,
    details TEXT NOT NULL CHECK (json_valid(details))
);
INSERT INTO parcels VALUES
    (1, '{"label":"Desk","weight":4,"note":null}'),
    (2, '{"label":"Shelf","weight":7}');
SELECT id,
       json_extract(details, '$.label'),
       json_extract(details, '$.weight'),
       typeof(json_extract(details, '$.weight')),
       json_type(details, '$.note')
FROM parcels ORDER BY id;

Run:

python3 run-sql.py read-json-fields.sql

Output:

1|Desk|4|integer|null
2|Shelf|7|integer|NULL

The weight appears as an SQL integer, so a later query can compare it numerically. The note column in the output deliberately differs: lowercase null is the JSON type name for an explicit JSON null; uppercase NULL is this runner’s rendering of an absent SQL value. json_extract() alone would return SQL NULL for either case.

The CHECK rejects malformed JSON text. It does not require an object, a label, or a positive weight. Add separate shape and business validation before relying on fields from external documents. NOT NULL also matters: a CHECK by itself is not a general substitute for rejecting missing input.

2. Turn an array into queryable rows

Save as expand-json-tags.sql:

CREATE TABLE articles (
    id INTEGER PRIMARY KEY,
    tags TEXT NOT NULL CHECK (json_valid(tags))
);
INSERT INTO articles VALUES
    (1, '["sqlite","linux"]'),
    (2, '[]'),
    (3, '["python"]');
SELECT a.id, tag.key, tag.value
FROM articles AS a, json_each(a.tags) AS tag
WHERE tag.type = 'text'
ORDER BY a.id, tag.key;

Run:

python3 run-sql.py expand-json-tags.sql

Output:

1|0|sqlite
1|1|linux
3|0|python

The first article contributes two rows, and key shows the zero-based array position. The second contributes none because its array is empty. This is useful for a tag report, but it would be the wrong query for a report that must retain every article. Plan an outer join when empty lists should remain visible.

json_each() visits the immediate members of its input, not every nested descendant. Here the type filter keeps only text elements. The schema still permits valid non-array JSON, so an application promising a list of tags should validate that contract too. For frequently edited relational tags, a separate article-tags table may be easier to constrain.

3. Change one setting without rebuilding the document

Save as update-json-settings.sql:

CREATE TABLE settings (
    id INTEGER PRIMARY KEY,
    document TEXT NOT NULL CHECK (json_valid(document))
);
INSERT INTO settings VALUES
    (1, '{"theme":"light","alerts":false}');
UPDATE settings
SET document = json_set(document,
    '$.theme', 'dark',
    '$.alerts', json('true'),
    '$.shortcuts', json('["search","help"]'))
WHERE id = 1;
SELECT document FROM settings WHERE id = 1;
SELECT json_type(document, '$.alerts'),
       json_type(document, '$.shortcuts')
FROM settings WHERE id = 1;

Run:

python3 run-sql.py update-json-settings.sql

Output:

{"theme":"dark","alerts":true,"shortcuts":["search","help"]}
true|array

The theme is an ordinary SQL string and becomes a JSON string. Wrapping the other two values with json() makes them a JSON boolean and array rather than strings containing JSON-looking text. The second query verifies the resulting types, not just their appearance in a terminal.

json_set() returns a changed document; the UPDATE stores it. Keep the WHERE clause when changing a specific account or setting. In application code, bind incoming values as parameters rather than building SQL by concatenation. Test both the document’s validity and the meaning of required fields after changing the data format.

SQLite’s JSON function reference documents paths, value handling, and array expansion. The 3.9.0 release notes identify the original extension, and the 3.38.0 release notes explain default inclusion. Use a supported current runtime rather than choosing an old release merely because it meets the syntax minimum.

Clean up

In the same terminal, remove only these example files and the empty scratch directory:

rm -- run-sql.py read-json-fields.sql expand-json-tags.sql update-json-settings.sql
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dir

Acknowledgements

This post was created with assistance from AI.