Keeping a JSON column honest in SQL

A TEXT column will happily store a half-written document unless you stop it.

CREATE TABLE settings (
    id INTEGER PRIMARY KEY,
    doc TEXT NOT NULL CHECK (JSON_VALID(doc)),
    owner TEXT NOT NULL
);

CREATE INDEX settings_theme
    ON settings (JSON_EXTRACT(doc, '$.theme'));

INSERT INTO settings (doc, owner)
VALUES ('{"theme":"dracula","tabs":4}', 'ada');

SELECT
    JSON_TYPE(doc, '$.tabs') AS tabs_type,
    doc ->> '$.theme' AS theme
FROM settings;

How it works

1. json_valid returns 1 or 0, which is exactly what a CHECK wants.
2. json_type names the kind at a path: object, array, integer, text.
3. Indexing an extracted path makes a JSON lookup as fast as a column.

Keywords and builtins used here

• AS
• CHECK
• CREATE
• FROM
• INDEX
• INSERT
• INTEGER
• INTO
• KEY
• NOT
• NULL
• ON
• PRIMARY
• SELECT
• TABLE
• TEXT
• VALUES
• owner

The run, in numbers

Lines

16

Characters to type

358

Tokens

76

Three-star pace

95 tpm

At the three-star pace of 95 tokens a minute, this run takes about 48 seconds.

Type this snippet

Step 4 of 5 in Writing JSON, step 7 of 14 in JSON & search.

← Previous Next →