When JSON Belongs in SQLite—and When Columns/Tables Are Better

Learning outcomes

JSON is useful in SQLite precisely because SQLite remains relational. The modeling question is not “columns or JSON?” as if one must win. The useful question is which facts deserve explicit relational structure and which genuinely variable details can remain inside a validated document.

Start with the data model, not a JSON function

Suppose a FieldNotes device has a device code, a site, an operational status, and a name. Those facts are stable, important to joins, and already constrained by the relational schema. Now imagine that different device families report optional details such as firmware channel, calibration certificate, network protocol, manufacturer-specific attributes, or a small list of descriptive tags. Forcing every possible vendor key into nullable columns can make the schema noisy; placing the stable identifiers themselves inside one opaque document makes integrity and querying harder.

SQLite does not gain a sixth JSON storage class

Chapter 4 established SQLite's five storage classes: NULL, INTEGER, REAL, TEXT, and BLOB. JSON support does not add another one. Current SQLite JSON functions accept ordinary SQLite values and interpret suitable TEXT as JSON text or suitable BLOB values as SQLite JSONB. Since SQLite 3.38.0, JSON support is built in by default, although a custom library can omit it with SQLITE_OMIT_JSON.

SELECT sqlite_version() AS sqlite_version,       json_valid('{"probe":true}') AS has_json_functions,       typeof('{"probe":true}') AS storage_class;

Expected on the course baseline: has_json_functions is 1 and the literal's storage class is text. If a bundled library reports “no such function: json_valid”, inspect that host's SQLite build instead of assuming the command-line version applies to the application.

Validate before you trust a path

A TEXT column can contain any text unless the schema says otherwise. Calling a JSON function on malformed text usually raises an error. json_valid(X) is deliberately safe for validation: with one argument it accepts strict RFC-8259 JSON text. SQLite can parse JSON5 input in newer versions, but one-argument json_valid() remains strict for backward-compatible validation.

SELECT json_valid('{"firmware":{"version":"3.7.2"}}') AS good,       json_valid('{firmware:{version:"3.7.2"}}') AS json5_but_not_strict,       json_valid('{"firmware":}') AS broken;-- If your contract intentionally accepts JSON5 (SQLite 3.42.0+):SELECT json_valid('{firmware:"3.7.2"}', 2) AS valid_json5;

A production contract should say whether stored text must be canonical JSON, merely parseable JSON5, or something else. Do not silently broaden the accepted format just because the parser can understand more syntax.

Build a hybrid FieldNotes table

The existing device table remains authoritative for identity, site, name, and status. A one-to-one profile table can hold optional device metadata while the foreign key preserves the relational link. The NOT NULL plus CHECK(json_valid(...)) combination prevents SQL NULL and malformed JSON text.

PRAGMA foreign_keys = ON;CREATE TABLE device_profile (  device_id INTEGER PRIMARY KEY            REFERENCES device(device_id) ON DELETE CASCADE,  installed_at TEXT NOT NULL,  metadata TEXT NOT NULL DEFAULT '{}'           CHECK (json_valid(metadata)));INSERT INTO device_profile(device_id, installed_at, metadata)VALUES  (1, '2026-08-01T09:00:00Z',   json_object(     'firmware', json_object('version','3.7.2','channel','stable'),     'tags', json_array('pump','critical'),     'network', json_object('protocol','modbus','port',502)   ));

json_object() and json_array() construct valid JSON instead of forcing the application to assemble punctuation by string concatenation.

Inspect a small property before learning full path syntax

A JSON path identifies a location inside a JSON value. For now, read $.firmware.version as “start at the document root, then enter firmware, then version.” Lesson 2 develops the full path mental model.

SELECT d.device_code,       json_extract(p.metadata, '$.firmware.version') AS firmware_version,       typeof(json_extract(p.metadata, '$.firmware.version')) AS sql_typeFROM device AS dJOIN device_profile AS p USING(device_id)WHERE d.device_id = 1;

Expected result: PUMP-007 | 3.7.2 | text. The path function returned an ordinary SQL TEXT value for this scalar JSON string.

Failure mode: duplicated facts create two truths

Do not place status in both device.status and device_profile.metadata merely because it is convenient for a JSON consumer. The moment one side changes without the other, the database contains contradictory facts.

UPDATE device_profileSET metadata = json_set(metadata, '$.status', 'retired')WHERE device_id = 1;SELECT d.status AS relational_status,       json_extract(p.metadata, '$.status') AS duplicated_json_statusFROM device AS dJOIN device_profile AS p USING(device_id)WHERE d.device_id = 1;

If the relational value says active while the document says retired, JSON has not made the design more flexible; it has made ownership ambiguous. The safe correction is to keep one authoritative representation and construct API JSON from it when needed.

Failure mode: hidden schema and hard-to-index paths

A document column still has a schema—it is simply implicit in application code and data conventions. A key named firmwareVersion in one row, firmware.version in another, and fw in a third produces a migration problem without a DDL statement announcing it. Likewise, a path that becomes central to filtering may deserve a generated column or expression index, which Lesson 5 will measure with EXPLAIN QUERY PLAN.

Lab: classify and store controlled metadata

Use a disposable copy of the FieldNotes database. Create device_profile, then add profiles for three devices. Keep core identity/status/site facts outside JSON; put optional firmware, tags, calibration, and network details inside.

INSERT INTO device_profile(device_id, installed_at, metadata) VALUES(2, '2026-07-18T11:30:00Z', json_object('firmware',json_object('version','2.4.1','channel','stable'),             'tags',json_array('fan','inspection'),             'calibration',json_object('due','2026-09-15','certified',1))),(3, '2026-08-05T08:15:00Z', json_object('firmware',json_object('version','1.9.0','channel','beta'),             'tags',json_array('sensor','vibration'),             'network',json_object('protocol','mqtt')));SELECT COUNT(*) AS profiles,       SUM(json_valid(metadata)) AS valid_documentsFROM device_profile;

Expected state after adding device 1 from the earlier example: profiles = 3 and valid_documents = 3.

Prove that invalid text is rejected

Run this only on the disposable lab database. The insert should fail at the database constraint, not later in a report.

CREATE TABLE json_validation_probe (  metadata TEXT NOT NULL CHECK(json_valid(metadata)));-- This must fail specifically because json_valid() returns 0.INSERT INTO json_validation_probe(metadata)VALUES ('{"firmware":}');

The important diagnostic habit is to isolate one negative condition at a time. If the same test also violated a foreign key or UNIQUE constraint, the observed error would not prove which layer rejected the malformed JSON.

Verification checkpoint

Production judgment and bridge

At this point JSON is a modeling option with a contract, not a bag of arbitrary text. Lesson 2 makes the document navigable and shows why extraction operators differ in the SQL values they return.

Authoritative references

• SQLite JSON Functions and Operators
• SQLite Datatypes
• SQLite Generated Columns
• SQLite 3.53.4 release history