JSON, generated columns and date functions

The built-in JSON functions and JSONB, generated columns over JSON, date and time arithmetic, and the string and math extensions.

JSON in a relational store

create table event (id integer primary key, body text) strict;

insert into event (body) values
  ('{"type":"order","total":1999,"items":[{"sku":"A1","qty":2}]}');

select json_extract(body, '$.type')                  as type,
       json_extract(body, '$.items[0].sku')          as first_sku,
       json_array_length(json_extract(body, '$.items')) as item_count
from event;

-- the -> and ->> operators
select body -> '$.type'    as as_json,     -- returns JSON text
       body ->> '$.type'   as as_text      -- returns a SQL value
from event;

-- build JSON from relational data
select json_object('id', id, 'type', json_extract(body, '$.type')) from event;

-- update a path in place instead of rewriting the whole document
update event
set body = json_set(body, '$.status', 'processed', '$.items[0].qty', 3)
where id = 1;

-- aggregate rows into a JSON array
select json_group_array(json_object('title', title, 'isbn', isbn)) from book;

-- validate before trusting a path
select id from event where json_valid(body) = 0;

• ->> returns a SQL value, -> returns JSON text. Comparisons need the first, so use it in WHERE clauses.
• A JSON path that does not exist yields NULL rather than an error, which makes a typo in a path look like missing data.
• json_each in a join turns an array into rows, which is how you filter on an item inside a document.
• jsonb stores the document in a parsed form: path lookups avoid re-parsing, and the storage is often smaller.

-- join to an array inside a document
select e.id, i.value ->> 'sku' as sku
from event e, json_each(e.body, '$.items') i
where i.value ->> 'sku' = 'A1';

-- stored in the compact binary form
update event set body = jsonb(body) where json_valid(body);
select json(body) from event;                  -- back to text when needed

Generated columns over JSON

create table event (
  id      integer primary key,
  body    text not null check (json_valid(body)),
  type    text generated always as (body ->> '$.type') stored,
  total   integer generated always as (cast(body ->> '$.total' as integer)) stored,
  created text generated always as (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')) virtual
) strict;

create index event_type_total on event (type, total);

select id, type, total from event where type = 'order' and total > 1000;

• A STORED generated column costs write time and disk but can be indexed directly, which is exactly what turns a JSON field into a fast query.
• A VIRTUAL column is computed on read: no storage, no index on the column itself, but an index on the expression is possible.
• Generated expressions must be deterministic and cannot reference other generated columns or use subqueries.
• Changing a stored column's expression requires recreating the table, so choose the extracted fields deliberately.

Dates, times and extensions

select date('now');                                  -- 2026-09-18
select datetime('now');                             -- UTC
select datetime('now', 'localtime');
select strftime('%Y-%m-%dT%H:%M:%SZ', 'now');
select unixepoch();                                 -- integer seconds
select julianday('now');
select date('now', '+1 month', 'start of month', '-1 day');   -- last day of the month
select strftime('%s', '2026-09-18') - strftime('%s', 'now') as seconds_left;

-- store timestamps as integers and format at the edges
create table reading (id integer primary key, sensor text, at integer not null, value real) strict;
insert into reading (sensor, at, value) values ('t1', unixepoch(), 21.5);
select sensor, datetime(at, 'unixepoch') as at_utc, value
from reading where at >= unixepoch() - 3600;

-- string and math helpers
select upper('abc'), trim('  x  '), replace('a-b', '-', '_');
select printf('%05d', 42), format('%s=%d', 'id', 7), substr('abcdef', 2, 3);
select abs(-3), round(3.14159, 2), max(1, 5, 3), sqrt(16), pow(2, 10);
select random(), randomblob(8), hex(randomblob(4));

• date() and friends need the modifier order you expect: date(x, '+1 month', 'start of month') applies modifiers left to right.
• now inside a single statement is fixed for that statement, so two rows inserted together get the same timestamp.
• localtime depends on the host timezone; storing UTC and converting in the application is more predictable.
• Math functions (sqrt, pow, ln) are compiled in by default since SQLite 3.35 but may be disabled in a custom build - check before relying on them.

FAQ

Related

Full-text search with FTS5 Data types, affinity, STRICT tables and WITHOUT ROWID

Last refreshed 2026-09-18.