JSON in SQLite

After this lesson you can pull typed values out of JSON documents, turn an array into rows you can group and sum, and build or edit documents in SQL. Fundamentals' CHECK constraints and the Indexes lesson's expression indexes both pay off here. The -> and ->> operators from PostgreSQL are a newer spelling that the grader's SQLite 3.27 rejects, so everything below uses the json1 functions.

Keeping the column honest

A document lives in an ordinary TEXT column. Nothing stops you storing {"name":"cam",, so add CHECK (json_valid(col)), and INSERT OR IGNORE skips the bad row silently:

CREATE TABLE gadgets (id INTEGER PRIMARY KEY, profile TEXT CHECK (json_valid(profile)));
INSERT OR IGNORE INTO gadgets VALUES
    (1, '{"name":"hub","ram":4,"price":9.5,"wifi":true,"note":null,"tags":["a","b"]}'),
    (2, '{"name":"cam",'),
    (3, '{"name":"tag","tags":[]}'),
    (4, '{"name":"bar"}');
SELECT COUNT(*) FROM gadgets;     -- 3

json_each over invalid text raises malformed JSON and the whole run fails.

What json_extract returns

json_extract(doc, path) returns an ordinary SQL value. A path starts at $, then .key and [index] (zero-based):

SELECT json_extract(profile, '$.name'),     -- hub    (text)
       json_extract(profile, '$.ram'),      -- 4      (integer)
       json_extract(profile, '$.price'),    -- 9.5    (real)
       json_extract(profile, '$.wifi'),     -- 1      (JSON true becomes 1)
       json_extract(profile, '$.tags'),     -- ["a","b"]  (JSON text)
       json_extract(profile, '$.tags[1]')   -- b
FROM gadgets WHERE id = 1;

A JSON null and a key that is not there both give SQL NULL, so json_extract cannot tell them apart. json_type(doc, path) can: it returns 'null' for the first and SQL NULL for the second, and names like 'integer' or 'array' otherwise.

Arrays become rows with json_each

json_each(doc, path) is a table-valued function: it returns one row per element, with the element in value and its position in key. Put it after a table in FROM and it reads that row's document:

SELECT g.id, t.value FROM gadgets g, json_each(g.profile, '$.tags') t;

1|a
1|b

Gadget 3 has an empty array and gadget 4 has no tags key; both produce zero rows, so they vanish from the result (on this SQLite a LEFT JOIN to json_each does not keep them either). To count elements without losing documents use json_array_length: SELECT id, json_array_length(profile, '$.tags') FROM gadgets; prints 1|2, 3|0, 4| (NULL for the missing key). The rows are ordinary rows, so GROUP BY and SUM apply directly. json_tree is the recursive version: one row per value at every level, with a fullkey such as $.tags.

Building and editing documents

SELECT json_object('id', 7, 'ok', json('true'), 'tags', json_array('x', 'y'));
-- {"id":7,"ok":true,"tags":["x","y"]}
SELECT json_group_array(n), json_group_object(n, id)
FROM (SELECT id, json_extract(profile, '$.name') AS n FROM gadgets ORDER BY id);
-- ["hub","tag","bar"]|{"hub":1,"tag":3,"bar":4}

json_group_array and json_group_object are aggregates; the order is whatever the rows arrive in, so sort in a subquery and treat that order as a convention, not a guarantee. Wrap 'true' in json() or json_object stores it as the string "true". Editing returns a new document; the column changes only through UPDATE. On '{"a":1,"b":2}':

The type trap, indexes, and when not to

A JSON string keeps its type: json_extract('{"v":"5"}', '$.v') > 9 is 1, because SQLite ranks any text above any number, while {"v":5} gives 0. Add + 0 to force a number, or fix the writer. To search one path quickly, index the same expression you filter on: CREATE INDEX idx_name ON gadgets (json_extract(profile, '$.name')) makes EXPLAIN QUERY PLAN print SEARCH TABLE gadgets USING INDEX idx_name (<expr>=?). A path you filter, join and sum on in every query deserves a real column; JSON fits shapes that genuinely vary.

Common mistakes

• Calling json_extract on an array and expecting rows: it returns the array as one text value like ["a","b"]. Only json_each gives one row per element.
• Treating a missing key as 0: it is NULL, and the flag true is the integer 1, so = 'true' matches nothing and WHERE ... = 0 misses documents without the key.
• Expecting a document with no array, or an empty one, to contribute a row: it contributes none.

Your exercise

Total the quantity of each sku across paid orders in the starter's orders table. The quantities sit inside items arrays, and the most likely mistake is letting an unpaid or flag-less order into the sums.