13. JSON in databases
- Understand when a JSON column is a good idea in a relational database (and when it is not).
- Query JSON inside SQLite:
json_extract,->>,json_each,json_type,json_object. - Use the three doors the API offers onto the
books.attributescolumn: the?format=filter,attributes.*fields in/query, SQL in/sql. - Know what changes in PostgreSQL (
jsonb) and in a document database (MongoDB).
Theory
Section titled “Theory”The books table has “normal” columns (title, price, author_id…) and an attributes column that holds JSON text:
{"format": "brossura", "cover": "morbida", "awards": [], "dimensions_cm": {"w": 15, "h": 19}, "ebook": false}Why not one column per attribute? Because the attributes are heterogeneous and changing: computer books have edition, novels do not; tomorrow audio_minutes shows up. A JSON column takes everything without an ALTER TABLE. The price: no per-key type constraint, slower queries unless indexed, and the temptation to also put in there what should be a real column (or table).
Rule of thumb: a normal column for what you filter, sort, join or constrain on (price, author, status); a JSON column for descriptive, variable, “long tail” properties.
SQLite’s JSON functions
Section titled “SQLite’s JSON functions”SQLite (always included since 3.38) offers:
| Function / operator | What it does |
|---|---|
json_extract(col, '$.a.b') |
extracts a value by path; $ is the root |
col ->> '$.a' |
like json_extract, but always returns an SQL value (text/number); -> returns JSON |
json_type(col, '$.a') |
the JSON type: text, integer, real, true, false, null, array, object |
json_each(col, '$.arr') |
a virtual table: one row per element of an array (it goes in the FROM) |
json_array_length, json_valid, json_object, json_group_array |
length, validity, building JSON from SQL rows |
And the detail that makes the difference in production: you can create an index on an expression, for example CREATE INDEX idx_format ON books(attributes ->> '$.format'), so a filter on the format does not scan the whole table.
curl, explained
Section titled “curl, explained”The /api/v1/sql endpoint only accepts SELECT (chapter 12 of Section 2 explains how it is locked down). The body is JSON with the query inside: watch the quotes.
curl -s -X POST https://corso-api-json.giuseppebosi.com/api/v1/sql \-H 'Content-Type: application/json' \-d '{"sql": "SELECT attributes ->> '"'"'$.format'"'"' AS format, COUNT(*) AS n, ROUND(AVG(price),2) AS avg_price FROM books GROUP BY 1 ORDER BY n DESC"}'- -X POST
- POST method: the query travels in the body, not in the URL
- -H 'Content-Type: application/json'
- declares that the body is JSON; without it, the server responds 422
- -d '{"sql": "…"}'
- data: the body. In single quotes, so the JSON double quotes stay intact
- '"'"'
- the trick for a single quote inside a single-quoted string: close
', open a double-quoted string containing', reopen'. Alternative: put the body in a file and use-d @query.json - attributes ->> '$.format'
- extracts the
formatfield from the column's JSON, as an SQL value - GROUP BY 1
- groups by the first column of the SELECT (the format)
{"columns":["format","n","avg_price"],"rows":[["tascabile",30,18.76],["brossura",26,22.49],["rilegato",24,18.52]],"row_count":3,"truncated":false,"elapsed_ms":1.19}With the body in a file you avoid the quoting acrobatics:
cat > q.json <<'FINE'{"sql": "SELECT b.title, j.value AS award FROM books b, json_each(b.attributes, '$.awards') j LIMIT 4"}FINEcurl -s -X POST https://corso-api-json.giuseppebosi.com/api/v1/sql -H 'Content-Type: application/json' -d @q.json- cat > q.json <<'FINE' … FINE
- heredoc: writes to the file everything between the two
FINEmarkers, without interpreting$(thanks to the quotes in'FINE') - -d @q.json
- the
@tells curl to read the body from the file - json_each(b.attributes, '$.awards') j
- for each book, one row per award in the array: an implicit JOIN between the table and its JSON elements
{"columns":["title","award"],"rows":[["Il cervello e Matera","Campiello"],["Il cervello e Matera","Bancarella"],["Contro Sestri","Campiello"],["Contro Sestri","Strega"]],"row_count":4,"truncated":false,"elapsed_ms":1.03}Three more queries to try (same way of calling):
-- tipi dei valori JSONSELECT json_type(attributes, '$.awards') AS t_awards, json_type(attributes, '$.ebook') AS t_ebook, json_array_length(attributes, '$.awards') AS n FROM books WHERE id = 1;-- → [["array","false",0]]
-- JSON dentro un'altra colonna JSON: l'indirizzo degli ordiniSELECT json_extract(shipping_address, '$.city') AS city, COUNT(*) AS nFROM orders GROUP BY 1 ORDER BY n DESC LIMIT 3;-- → [["Torino",25],["Padova",22],["Firenze",19]]
-- costruire JSON da righe relazionali: il libro con i suoi tag (tabella N-N) in un documentoSELECT json_object('id', id, 'title', title, 'tags', (SELECT json_group_array(t.name) FROM tags t JOIN book_tags bt ON bt.tag_id = t.id WHERE bt.book_id = b.id)) AS docFROM books b WHERE id IN (1, 2);-- → {"id":2,"title":"TypeScript e Ferrara","tags":["bestseller","tascabile"]}The last one is interesting: it is exactly what an API does when it answers GET /books/2?expand=tags. The JSON you receive from the API is born like this, from a join turned into a document.
Python and TypeScript
Section titled “Python and TypeScript”The three doors onto the JSON column, from the simplest to the most powerful.
import httpxBASE = "https://corso-api-json.giuseppebosi.com/api/v1"
# 1) filtro dedicato: l'API traduce ?format= in json_extract(attributes,'$.format') = ?r = httpx.get(f"{BASE}/books", params={"format": "rilegato", "limit": 2}).json()print(r["meta"]["total"], [b["title"] for b in r["items"]])# 24 ['Andromeda e il Novecento', 'Le mura di Norcia']
# 2) query strutturata: campi "attributes.x" ammessi nel where e nel selectq = {"from": "books", "select": ["id", "title", "price", "attributes.format"], "where": {"and": [{"field": "attributes.format", "op": "eq", "value": "tascabile"}, {"field": "price", "op": "lt", "value": 10}]}, "order_by": ["price"], "limit": 3}r = httpx.post(f"{BASE}/query", json=q).json()print(r["meta"]["sql"]) # guarda come è stato tradottofor b in r["items"]: print(b["price"], b["title"], b["attributes.format"])
# 3) SQL libero (solo SELECT): json_each per "esplodere" un arraysql = "SELECT b.title, j.value AS award FROM books b, json_each(b.attributes, '$.awards') j LIMIT 4"r = httpx.post(f"{BASE}/sql", json={"sql": sql}).json()print(r["columns"], r["rows"])Output (excerpt):
SELECT id, title, price, json_extract(attributes, '$.format') AS "attributes.format" FROM books WHERE (json_extract(attributes, '$.format') = ? AND price < ?) ORDER BY price ASC LIMIT 3 OFFSET 07.13 La cucina di Ferrara tascabile7.25 Contro Aosta tascabile7.91 Il valore di Aosta tascabile['title', 'award'] [['Il cervello e Matera', 'Campiello'], …]Want to try the same functions locally, with no server? sqlite3 is in the standard library:
import sqlite3, jsoncon = sqlite3.connect(":memory:")con.execute("CREATE TABLE b(title, attributes)")con.execute("INSERT INTO b VALUES (?, ?)", ("Prova", json.dumps({"format": "ebook", "awards": ["Strega"]})))con.execute("CREATE INDEX i ON b(attributes ->> '$.format')") # indice su espressioneprint(con.execute("SELECT title, attributes ->> '$.format', json_array_length(attributes, '$.awards') FROM b").fetchall())# [('Prova', 'ebook', 1)]What you should see:
[('Prova', 'ebook', 1)]// node --experimental-strip-types jsondb.tsconst BASE = "https://corso-api-json.giuseppebosi.com/api/v1";const post = async (path: string, body: unknown) => (await fetch(`${BASE}${path}`, { method: "POST", headers: { "Content-Type": "application/json" }, body: JSON.stringify(body) })).json();
// 1) filtro dedicatoconst r1 = await (await fetch(`${BASE}/books?format=rilegato&limit=2`)).json();console.log(r1.meta.total, r1.items.map((b: any) => b.title));
// 2) query strutturata con campi attributes.*const r2 = await post("/query", { from: "books", select: ["id", "title", "price", "attributes.format"], where: { and: [{ field: "attributes.format", op: "eq", value: "tascabile" }, { field: "price", op: "lt", value: 10 }] }, order_by: ["price"], limit: 3,});console.log(r2.meta.sql);for (const b of r2.items) console.log(b.price, b.title, b["attributes.format"]);
// 3) SQL (solo SELECT)const r3 = await post("/sql", { sql: "SELECT b.title, j.value AS award FROM books b, json_each(b.attributes, '$.awards') j LIMIT 4" });console.log(r3.columns, r3.rows);What you should see:
24 [ 'Andromeda e il Novecento', 'Le mura di Norcia' ]SELECT id, title, price, json_extract(attributes, '$.format') AS "attributes.format" FROM books WHERE (json_extract(attributes, '$.format') = ? AND p…7.13 La cucina di Ferrara tascabile7.25 Contro Aosta tascabile7.91 Il valore di Aosta tascabile[ 'title', 'award' ] [ [ 'Il cervello e Matera', 'Campiello' ], [ 'Il cervello e Matera', 'Bancarella' ], [ 'Contro Sestri', 'Campiello' ], [ 'Contro Sestri', 'Strega' ]]Locally, Node 22 includes a built-in SQLite driver: import { DatabaseSync } from "node:sqlite" (flag --experimental-sqlite, documentation), with the same json_* functions because it is the same engine. Alternatively npm install better-sqlite3.
Try it now
Section titled “Try it now”Response headers
Response headers
Response headers
Exercise
Section titled “Exercise”Try it online, nothing to install:
The GET request straight in your browser: https://corso-api-json.giuseppebosi.com/api/v1/books?format=tascabile&limit=3
Python on Google Colab (a free Google account is needed to run the cells); TypeScript on StackBlitz (it opens and starts by itself in the browser). If the practice API is switched off, turn it on from /en/api-attiva/.
- With
/sql: how many books have at least one award? (hint:json_array_length(attributes, '$.awards') > 0). - With
/sql: the average height (dimensions_cm.h) of the books per format. - With
/query: the books inrilegato(hardcover) format or withebook = true, sorted by descending price, first 5.
Solution
SELECT COUNT(*) FROM books WHERE json_array_length(attributes, '$.awards') > 0; -- 29SELECT attributes ->> '$.format' AS f, ROUND(AVG(attributes ->> '$.dimensions_cm.h'), 1) AS h FROM books GROUP BY 1;-- brossura 20.7 · rilegato 21.1 · tascabile 21.1{"from": "books", "where": {"or": [{"field": "attributes.format", "op": "eq", "value": "rilegato"}, {"field": "attributes.ebook", "op": "eq", "value": true}]}, "order_by": ["-price"], "limit": 5}(prices 34.36, 33.45, 32.71, 31.27, 31.04). Note in the second query: ->> returns a number when the JSON value is numeric, so AVG works without a cast.
Common mistakes
Section titled “Common mistakes”json_extract(col, 'format')without$.: the path must start from the root$.- Comparing
->with a string:->returns JSON (with the quotes!),->>the value. InWHEREclauses almost always use->>. - Booleans: in SQLite JSON
true/falsebecome1/0with->>(you can see it in the widget above:attributes.ebook: 1);json_typeinstead saystrue/false. - Everything in JSON: if you filter and join on a key every day, promote it to a real column (or table).
- Forgetting the expression index when the JSON column grows: the query stays correct but scans the whole table.
Summary
Section titled “Summary”A JSON column gives flexibility where the schema varies; SQLite and PostgreSQL can extract, filter, explode and index that JSON, and a good API exposes those capabilities in a controlled way. In the next chapter: JSON over HTTP, headers, bodies and errors.