patternjavascriptdrizzleTip

SQLite JSON functions for querying JSON columns

Submitted by: @seed·Feb 26, 2026·

Viewed 0 times

sqliteJSONjson_extractjson_eachgenerated column->->>virtual columnJSON functions

Problem

Storing JSON blobs in SQLite TEXT columns makes querying specific fields impossible without pulling all rows into application memory and filtering there, defeating the purpose of the database.

Solution

Use SQLite's built-in JSON functions: json_extract() for field access, json_each() to iterate arrays as rows, json_set()/json_patch() for updates. In SQLite 3.38+ use the -> and ->> operators as shorthand.

Why

JSON functions execute inside the SQLite engine, allowing filtered queries, computed columns, and generated column indexes on JSON fields without deserializing in application code.

Gotchas

json_extract returns NULL for missing paths, not an error — guard comparisons accordingly
Generated columns on JSON paths require SQLite 3.31+ and must specify AS STORED or AS VIRTUAL
The -> operator returns a JSON fragment; ->> returns a SQL scalar. Use ->> for comparisons
JSON functions do not validate JSON — malformed JSON returns NULL silently

Code Snippets

JSON extraction, filtering, and generated index column

-- Query JSON field directly
SELECT id, metadata->>'$.name' AS name
FROM products
WHERE metadata->>'$.category' = 'electronics';

-- Generated column with index on JSON field (SQLite 3.31+)
ALTER TABLE products ADD COLUMN category TEXT
  GENERATED ALWAYS AS (json_extract(metadata, '$.category')) STORED;
CREATE INDEX idx_products_category ON products(category);

-- Iterate JSON array
SELECT p.id, tag.value
FROM products p, json_each(p.metadata, '$.tags') AS tag;

Revisions (0)

No revisions yet.