Skip to content
Tutorials Center
My learning

Topics

Lesson 18 of 33 ยท Schema Design and Integrity

Generated Columns and JSON

Use SQLite generated columns (virtual vs stored) and the JSON functions json_extract, -> and ->>, json_each and json_group_array, and index JSON expressions.

Two features help you keep derived and semi-structured data in one place. A generated column is a column whose value SQLite computes from other columns, so you never have to keep it in sync by hand. The JSON functions let you store flexible documents in a plain TEXT column and still query inside them with SQL. Together they give you a practical middle path: a normal table for the stable fields, a JSON column for the loose ones, and generated columns plus indexes to make the JSON parts fast.

Both features are built into modern SQLite (generated columns since 3.31, the -> and ->> operators since 3.38) and both run in the live editor below. For a refresher on how SQLite stores values, see Dynamic typing and type affinity.

Computed columns

VIRTUAL Computed when you read it. No storage.
STORED Computed on write. Takes space, can be indexed.

Virtual columns can be indexed too.

1 / 2

Generated columns

Declare one with GENERATED ALWAYS AS (expression), followed by VIRTUAL (the default) or STORED. The expression may use other columns of the same row and deterministic functions; it cannot use subqueries, random() or the current time, and you cannot write to the column yourself.

SQL VIRTUAL and STORED columns

Press Run (or Ctrl + Enter) to see the result here.

total and label both change when qty changes, with no extra code. The difference is where the work happens. A VIRTUAL column costs nothing in the file but is recalculated on every read. A STORED column is calculated when the row is written and takes space, so reads are cheap. Choose VIRTUAL unless the expression is expensive and read often.

Rules worth knowing:

  • Inserting into a generated column is an error ("cannot INSERT into generated column"); list the other columns by name.
  • ALTER TABLE ... ADD COLUMN can add only VIRTUAL generated columns.
  • Generated columns can have indexes, NOT NULL or CHECK constraints, and a type that applies affinity to the result.
  • SELECT * includes them like any other column.

JSON in SQLite

SQLite has no separate JSON column type. You store JSON as ordinary text, and the json_* functions parse that text when you call them. json_valid(text) tells you whether a string is well-formed, and a CHECK (json_valid(col)) constraint keeps junk out of the column.

Getting values out: json_extract, -> and ->>

json_extract(doc, path) pulls a value out using a path that starts at $: $.name for an object field, $.tags[0] for the first array item, $.address.city for nesting. SQLite 3.38 added two operators borrowed from other databases:

Expression Returns
json_extract(doc, '$.age') An SQL value: text, integer, real or NULL. Objects and arrays come back as JSON text.
doc ->> '$.age' The same as json_extract (an SQL value).
doc -> '$.age' The element as a JSON value (strings keep their quotes).
SQL Reading values from JSON

Press Run (or Ctrl + Enter) to see the result here.

A missing path gives NULL, as for cy's age and city; there is no error. The second query shows the difference between the operators: -> returns the JSON text "ana" with the quotes, and ->> returns the plain text ana. The last line shows that unquoted keys are not valid JSON. For conditions in WHERE, you nearly always want ->>, because it compares as a normal SQL value:

SQL
SELECT id FROM events WHERE payload ->> '$.age' >= 18;

Changing JSON

The writing functions return a new JSON string, so you use them in an UPDATE: json_set (set or create), json_insert (create only), json_replace (replace only), json_remove and json_patch (merge a patch). json_object(...) and json_array(...) build values from SQL expressions.

SQL Updating and building JSON

Press Run (or Ctrl + Enter) to see the result here.

json_set bumped visits to 2 and created a vip key (wrapped in json('true') so it is a real boolean, not the string "true"). json_remove took it away again. The last query builds a fresh document from column values. Note that JSON text is rewritten whole on every change, so very large documents updated often are better as normal columns.

Arrays: json_each and json_group_array

json_each(doc, path) is a table-valued function that returns one row for every element of an array (or every member of an object) with columns key, value, type and more. Joined to your table, it turns an array into rows you can filter, group and count. json_group_array(expr) is the opposite: an aggregate that collects values back into a JSON array. json_group_object(k, v) does the same for objects.

SQL Arrays to rows and back

Press Run (or Ctrl + Enter) to see the result here.

The first query lists seven title and tag pairs. The second counts posts per tag: sql, sqlite and beginner each appear twice (the order is alphabetical among the ties), advanced once. The third gathers the titles of posts tagged sqlite into a JSON array, and the last uses EXISTS with json_each to test for membership. Keep in mind that json_each cannot use an ordinary index, so this scans every row; if you filter by tag a lot, a real post_tags table (see the foreign keys lesson) is the better design.

Indexing JSON expressions

Querying payload ->> '$.user' in a WHERE clause scans the whole table, parsing each document. There are two ways to make it fast, and both rely on indexes you already know from Indexes.

  1. An index on the expression: CREATE INDEX events_user ON events (payload ->> '$.user'). SQLite uses it when your query contains exactly the same expression.
  2. A generated column plus an index: give the value a real column name, then index that.
SQL Generated column plus index on JSON

Press Run (or Ctrl + Enter) to see the result here.

The query finds ana's two events, using plain column syntax. The plan's detail column reads SEARCH events USING INDEX events_user (user=?), proving the index is used instead of a scan. A virtual generated column costs no space; only the index stores the extracted values. The next lessons on indexes and query plans explain how to read that output in detail.

Use JSON for fields that really vary per row (settings, event payloads, API responses). Keep identifiers, foreign keys and anything you join or sort by in normal columns, with constraints.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What is the difference between VIRTUAL and STORED generated columns?

    A virtual column takes no space and is recalculated when read. A stored column is calculated when the row is written and takes disk space.
  2. 2. What does doc ->> '$.user' return for a JSON string value?

    ->> returns an SQL value, so a JSON string becomes ordinary text. The single arrow -> returns a JSON value that keeps the quotes.
  3. 3. What do you get from doc ->> '$.missing' when the key does not exist?

    A missing path gives NULL. Validate the structure with json_valid and CHECK constraints if you need stricter rules.
  4. 4. Which function turns each element of a JSON array into its own row?

    json_each is a table-valued function that yields one row per array element or object member. json_group_array does the reverse and builds an array.
  5. 5. How can you make WHERE payload ->> '$.user' = 'ana' fast on a big table?

    An index on the same expression (or on a generated column computing it) lets SQLite look up values instead of parsing every document.

Summary

  • Generated columns compute their value from other columns; VIRTUAL is computed on read, STORED on write, and both can be indexed.
  • JSON lives in ordinary TEXT; guard it with CHECK (json_valid(col)).
  • ->> and json_extract give SQL values, -> gives JSON values, and a missing path is NULL.
  • json_set, json_remove and friends return modified JSON for use in UPDATE.
  • json_each turns arrays into rows and json_group_array turns rows into arrays.
  • Index a JSON expression directly, or through a generated column, to avoid scanning and parsing every row.

Next: Indexes, making queries fast.