JSON Functions

SQLite can store and query JSON directly inside a normal TEXT column. The functions come from the JSON1 extension, which is compiled into essentially every modern SQLite build — since SQLite 3.38 (2022) the JSON functions are simply built in, no extension loading required. This makes it practical to keep small, variable-shaped data — like a list of tags — as JSON in one column instead of a separate table.

Storing JSON: RecipeBox Tags

Instead of a separate tags table, RecipeBox can store each recipe's tags as a JSON array directly in the recipes row:

CREATE TABLE recipes (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  tags TEXT -- JSON array, e.g. '["vegetarian","quick","dinner"]'
);

INSERT INTO recipes (title, tags)
VALUES ('Veggie Stir Fry', '["vegetarian","quick","dinner"]');

json_extract() and the -> / ->> Shorthand

json_extract(json, path) pulls a value out of a JSON document using a path expression. Since SQLite 3.38, the -> and ->> operators do the same thing with shorter syntax: -> returns the result as JSON, while ->> returns it as a plain SQL text/numeric value (the one you usually want in a WHERE clause):

-- Get the first tag in the array
SELECT json_extract(tags, '$[0]') FROM recipes;

-- Same thing with the ->> shorthand operator
SELECT tags ->> '$[0]' FROM recipes;

-- For an object column: {"unit":"metric"} -> '$.unit' gives 'metric' as text
SELECT preferences ->> '$.unit' FROM recipes;

Building JSON: json_object() and json_array()

json_object() and json_array() construct JSON values from ordinary SQL arguments — handy for shaping API-style output directly in a query:

SELECT json_object('id', id, 'title', title) FROM recipes;
-- {"id":1,"title":"Veggie Stir Fry"}

SELECT json_array('vegetarian', 'quick', 'dinner');
-- ["vegetarian","quick","dinner"]

-- How many tags does each recipe have?
SELECT title, json_array_length(tags) AS tag_count FROM recipes;

Worked Example: Finding Recipes by Tag with json_each()

json_each(json) is a table-valued function — it expands a JSON array or object into rows, so you can join against it just like a regular table. This is how RecipeBox finds every recipe tagged 'vegetarian' even though the tags live inside one JSON column:

SELECT DISTINCT r.id, r.title
FROM recipes r, json_each(r.tags) AS t
WHERE t.value = 'vegetarian';

Each row json_each() produces has a key (the array index or object key), a value, and several other columns describing the element's type and path — here we just filter on value to match the tag we want. json_tree() works the same way but also recurses into nested arrays/objects.