Learn

SQLite supports JSON via the json1 extension (built-in since 3.38). Postgres has jsonb. MySQL has JSON. The basics are similar across engines.

Storing JSON:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    payload TEXT             -- store as text, validated as JSON
);
INSERT INTO events VALUES
    (1, '{"type": "login", "user_id": 42, "ts": "2026-05-08"}'),
    (2, '{"type": "purchase", "user_id": 42, "items": ["a", "b"]}');

Extract values with json_extract:

SELECT id,
       json_extract(payload, '$.type') AS type,
       json_extract(payload, '$.user_id') AS user_id
FROM events;

The -> and ->> operators (SQLite 3.38+, also Postgres):

• -> returns JSON
• ->> returns text (unwrapped)

SELECT payload->'type' FROM events;       -- JSON value (with quotes for strings)
SELECT payload->>'type' FROM events;      -- text value (no quotes)

Path notation $, $.field, $.array[0], $.deep.nested.path:

SELECT json_extract(payload, '$.items[0]') FROM events WHERE id = 2;

Filter on JSON:

SELECT * FROM events
WHERE json_extract(payload, '$.type') = 'purchase';

-- Or with the operator:
SELECT * FROM events WHERE payload->>'type' = 'purchase';

Build JSON:

SELECT json_object('name', 'Ada', 'age', 36);
-- {"name":"Ada","age":36}

SELECT json_array(1, 2, 3, 4);
-- [1,2,3,4]

json_each / json_tree — table-valued functions that explode JSON into rows:

SELECT key, value FROM json_each('{"a": 1, "b": 2}');
-- a, 1
-- b, 2

SELECT value FROM json_each('[1, 2, 3]');
-- 1, 2, 3

Indexing JSON paths (Postgres-style; SQLite has limited support):

CREATE INDEX idx_event_type ON events (json_extract(payload, '$.type'));

When to use JSON columns:

• Schema is genuinely flexible (event types vary, user attributes change)
• You need a small subset of SQL filtering
• You don't need to JOIN heavily on the JSON fields

When NOT to:

• The schema is stable — use real columns
• You need fast aggregations / joins on the JSON fields — relational normalization wins