JSON & search
14 steps in 4 sets of SQL.
SQL absorbed the document databases' better ideas rather than losing to them. Reading JSON out of a column, writing it back, and then full-text search with FTS5.
Fourteen steps. Worth knowing before you decide you need a second database alongside the one you already have.
Reading JSON
- Reading JSON out of a columnA JSON column is text until you ask a JSON function to look inside it.
- The arrow operatorsTwo arrows, one difference: one keeps the JSON quoting and one strips it.
- Turning an array into rowsjson_each is a table: one row per element, which puts JSON back inside SQL's reach.
Writing JSON
- Building JSON in a queryThe other direction: rows in, one JSON document out.
- Aggregating rows into JSONOne document per group, assembled by the database instead of by the caller.
- Changing JSON in placeEditing a document without parsing it in your application first.
- Keeping a JSON column honestA TEXT column will happily store a half-written document unless you stop it.
- A JSON API response, in SQLOne query, one document: the shape the caller wanted, assembled where the data lives.
Full-text search
- A full-text indexLIKE scans every row. A full-text index knows which rows hold the word.
- Matching textThe MATCH operator takes a small query language of its own.
- Ranking and highlighting matchesA search result needs an order and a preview, and FTS5 has both built in.
- Keeping a search index in syncThe index should not be a second copy of the truth, so point it at the table and keep it fed.
Encore
- search_index.sqlA working search feature: an index over an existing table, kept in sync, queried and ranked.
- json_ingest.sqlA JSON document arrives; relational rows come out, with the bad records set aside.