SQLite JSON: json_extract, json_each, and All 14 JSON Functions

Create a table and store JSON in SQLite

SQLite stores JSON as plain TEXT — there is no dedicated JSON column type. The practical convention is to declare the column as TEXT and add a CHECK(json_valid(data)) constraint, which SQLite evaluates on every INSERT and UPDATE and rejects malformed JSON with a "CHECK constraint failed" error. This enforces the same validity guarantee that MySQL's native JSON type provides automatically. SQLite 3.38.0 (February 2022) made the JSON1 extension a permanent built-in; on earlier builds (3.9.0+) you can verify availability with SELECT json('{}') — if it returns {} JSON1 is present. A NOT NULL constraint on the JSON column is optional but recommended for required metadata fields.

CREATE TABLE products (
  id    INTEGER PRIMARY KEY,
  name  TEXT    NOT NULL,
  data  TEXT    NOT NULL CHECK(json_valid(data))
);

INSERT INTO products (name, data) VALUES
  ('Widget A', '{"price": 9.99, "category": "tools", "tags": ["sale", "new"]}'),
  ('Widget B', '{"price": 24.99, "category": "electronics", "tags": ["premium"]}'),
  ('Widget C', '{"price": 4.49, "category": "tools", "status": "active", "stock": 100}');

-- Verify JSON1 is available (returns '{}' if present)
SELECT json('{}');

-- Check validity before inserting
SELECT json_valid('{"price": 9.99}');  -- 1
SELECT json_valid('{bad json}');        -- 0

You can insert NULL into a nullable JSON column (representing an absent value), but json_valid(NULL) returns NULL, not 0, so the CHECK constraint does not block it. If you want to disallow NULL while still accepting all valid JSON, combine NOT NULL with the CHECK constraint as shown above. Use Jsonic's JSON Validator to catch problems before they hit the database constraint.

Query JSON fields with json_extract()

json_extract(column, path) is the primary read function in SQLite JSON. It returns a native SQLite value — TEXT for JSON strings, INTEGER or REAL for numbers, NULL for missing paths, and a JSON string for nested objects or arrays. Path expressions start with $ (the document root): $.key reads a top-level field, $.address.city reads a nested field, $.tags[0] reads the first array element (zero-indexed), and $.tags[#-1] reads the last element. SQLite 3.38.0 added the shorthand operators -> (returns a JSON value) and ->> (returns an unwrapped SQL value), equivalent to MySQL's operators introduced in 5.7.9.

-- Read a top-level scalar
SELECT name, json_extract(data, '$.price') AS price
FROM products;

-- Filter by a JSON field
SELECT name FROM products
WHERE json_extract(data, '$.category') = 'electronics';

-- Nested path
SELECT json_extract(data, '$.address.city') AS city FROM products;

-- Array element (zero-indexed)
SELECT json_extract(data, '$.tags[0]') AS first_tag FROM products;

-- Last array element
SELECT json_extract(data, '$.tags[#-1]') AS last_tag FROM products;

-- SQLite 3.38.0+ shorthand operators
SELECT data -> '$.price'   FROM products;   -- JSON value (quoted string)
SELECT data ->> '$.price'  FROM products;   -- unwrapped SQL value

-- NULL for a missing path
SELECT json_extract(data, '$.discount') FROM products;  -- NULL if absent

Because json_extract returns a native type, arithmetic comparisons work directly: WHERE json_extract(data, '$.price') > 10 needs no casting. If you need to test whether a path exists, compare the result to NULL: WHERE json_extract(data, '$.discount') IS NOT NULL.

Iterate arrays with json_each() and json_tree()

json_each(column) and json_tree(column) are virtual table functions used in the FROM clause to iterate JSON structures as rows. json_each walks only the immediate children of the target node — one row per direct array element or per top-level object key. json_tree performs a full recursive descent through every node at every nesting level. Both expose 8 columns: key, value, type, atom, id, parent, fullkey, and path. The type column returns one of 8 type strings: null, true, false, integer, real, text, array, or object. Pass an optional second argument to start iteration at a sub-path.

-- Expand $.tags array: one row per tag per product
SELECT p.name, e.value AS tag
FROM products p, json_each(p.data, '$.tags') e;
-- Returns: Widget A | sale, Widget A | new, Widget B | premium …

-- Filter products that have a specific tag
SELECT DISTINCT p.name
FROM products p, json_each(p.data, '$.tags') e
WHERE e.value = 'sale';

-- Walk all key-value pairs of the top-level object
SELECT e.key, e.value, e.type
FROM json_each('{"price": 9.99, "tags": ["a","b"], "active": true}') e;

-- json_tree: recursive descent through every node
SELECT jt.fullkey, jt.type, jt.atom
FROM products p, json_tree(p.data) jt
WHERE p.id = 1;

-- Start json_each at a sub-path
SELECT e.value
FROM products p, json_each(p.data, '$.tags') e
WHERE p.id = 1;

Use json_each for the common pattern of unnesting an array column into rows for aggregation or joining. Use json_tree when you need to search for a value anywhere in a deeply nested document regardless of its path — for example, finding all nodes of a given type.

Modify JSON: json_set(), json_insert(), json_replace(), json_remove()

SQLite's 4 mutation functions all return a new JSON text value — SQLite does not support in-place JSON mutation, so you must assign the result back to the column in an UPDATE statement. The functions differ in how they handle existing vs. absent paths, exactly mirroring the behavior of MySQL's equivalents. You can chain multiple path-value pairs in a single call to make several changes atomically: json_set(data, '$.price', 19.99, '$.stock', 50). Paths that do not exist are silently ignored by json_replace and paths that already exist are silently ignored by json_insert, making each function safe for conditional updates without needing a CASE expression.

-- json_set: upsert — update existing or add missing key
UPDATE products
SET data = json_set(data, '$.price', 14.99)
WHERE id = 1;

-- json_insert: add only if key is absent (safe default)
UPDATE products
SET data = json_insert(data, '$.discount', 0.1)
WHERE id = 1;

-- json_replace: update only if key exists
UPDATE products
SET data = json_replace(data, '$.price', 12.99)
WHERE id = 1;

-- json_remove: delete a key
UPDATE products
SET data = json_remove(data, '$.discount')
WHERE id = 1;

-- Chain multiple changes in one call
UPDATE products
SET data = json_set(data, '$.price', 9.99, '$.stock', 50, '$.status', 'active')
WHERE id = 1;

-- Nested path
UPDATE products
SET data = json_set(data, '$.address.city', 'Austin')
WHERE id = 1;

json_patch(target, patch) is also available since SQLite 3.31.0 and implements RFC 7396 merge-patch semantics: keys in the patch overwrite matching keys in the target, and patch keys with a JSON null value remove the corresponding key from the target. This is the SQLite equivalent of MySQL's JSON_MERGE_PATCH().

All 14 SQLite JSON functions

SQLite 3.38.0 ships 14 JSON functions covering validation, construction, reading, modification, aggregation, and iteration. The table below lists every function, its purpose, and a minimal example. The 2 aggregate functions — json_group_array and json_group_object — are used with GROUP BY to build JSON from relational rows, similar to MySQL's JSON_ARRAYAGG(). The json_array_length function accepts an optional path argument to measure a nested array without extracting it first.

-- json_type: get the type of a value or path
SELECT json_type(data)              FROM products;  -- 'object'
SELECT json_type(data, '$.tags')    FROM products;  -- 'array'
SELECT json_type(data, '$.price')   FROM products;  -- 'real' or 'integer'

-- json_array_length: count items in $.tags
SELECT name, json_array_length(data, '$.tags') AS tag_count
FROM products;

-- json_group_array: aggregate names per category
SELECT json_extract(data, '$.category') AS category,
       json_group_array(name)           AS names
FROM products
GROUP BY json_extract(data, '$.category');

-- json_group_object: build a {name: price} lookup
SELECT json_group_object(name, json_extract(data, '$.price')) AS price_map
FROM products;

-- json_patch: partial update via RFC 7396
UPDATE products
SET data = json_patch(data, '{"price": 19.99, "on_sale": true}')
WHERE id = 1;

Index JSON fields with expression indexes

SQLite supports expression indexes — indexes on the result of any deterministic function call, including json_extract. Once you create the index, anyWHERE clause using exactly the same expression benefits from an O(log n) B-tree lookup instead of a full table scan. SQLite version 3.9.0 (October 2015) introduced expression indexes; they work in all subsequent releases. The expression in the index definition must match the query expression exactly — same function name, same path string, same column name — for SQLite's query planner to recognize and use it. Run EXPLAIN QUERY PLAN to confirm the index is in use. Partial expression indexes are supported and keep the index small when only a subset of rows need fast lookup.

-- Create an expression index on $.status
CREATE INDEX idx_status
  ON products(json_extract(data, '$.status'));

-- This query uses idx_status (O(log n))
SELECT name FROM products
WHERE json_extract(data, '$.status') = 'active';

-- Confirm with EXPLAIN QUERY PLAN
EXPLAIN QUERY PLAN
SELECT name FROM products
WHERE json_extract(data, '$.status') = 'active';
-- SEARCH products USING INDEX idx_status …

-- Compound expression index on two JSON paths
CREATE INDEX idx_cat_price
  ON products(
    json_extract(data, '$.category'),
    json_extract(data, '$.price')
  );

-- Partial index: only index active products
CREATE INDEX idx_active_price
  ON products(json_extract(data, '$.price'))
  WHERE json_extract(data, '$.status') = 'active';

-- Drop the index when no longer needed
DROP INDEX IF EXISTS idx_status;

Expression indexes do not support JSON array membership queries the way MySQL 8.0.17+ multi-valued indexes do. For array membership in SQLite, use a EXISTS subquery with json_each: WHERE EXISTS (SELECT 1 FROM json_each(p.data, '$.tags') e WHERE e.value = 'sale'). For detailed comparison with PostgreSQL's GIN indexing, see the PostgreSQL JSON and JSONB guide.

Python and Node.js query examples

Python's built-in sqlite3 module uses SQLite 3.x and supports JSON1 without any additional packages — you pass JSON as a string parameter and call json_extract directly in your SQL. Node.js has no bundled SQLite driver; the most popular package is better-sqlite3, which provides a synchronous API and bundles its own SQLite build with JSON1 enabled. In both environments, pass Python dict or JavaScript object values by serializing them to JSON strings before binding, and parse the extracted scalar values back to native types as needed. The json_group_array aggregate function is especially useful from application code to retrieve a list of records as a single JSON array, reducing round-trips.

# Python — built-in sqlite3 module
import sqlite3, json

con = sqlite3.connect("store.db")
cur = con.cursor()

# Create table with CHECK constraint
cur.executescript("""
  CREATE TABLE IF NOT EXISTS products (
    id   INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    data TEXT NOT NULL CHECK(json_valid(data))
  );
""")

# Insert a row — serialize dict to JSON string
product = {"price": 9.99, "category": "tools", "tags": ["sale", "new"]}
cur.execute(
    "INSERT INTO products (name, data) VALUES (?, ?)",
    ("Widget A", json.dumps(product))
)
con.commit()

# Query a JSON field
rows = cur.execute(
    "SELECT name, json_extract(data, '$.price') FROM products "
    "WHERE json_extract(data, '$.category') = ?",
    ("tools",)
).fetchall()
print(rows)  # [('Widget A', 9.99)]

# Expand tags array into rows
tags = cur.execute(
    "SELECT p.name, e.value "
    "FROM products p, json_each(p.data, '$.tags') e"
).fetchall()
print(tags)  # [('Widget A', 'sale'), ('Widget A', 'new')]

# Update a JSON field
cur.execute(
    "UPDATE products SET data = json_set(data, '$.price', ?) WHERE id = ?",
    (14.99, 1)
)
con.commit()
con.close()

// Node.js — better-sqlite3 (npm install better-sqlite3)
const Database = require('better-sqlite3');
const db = new Database('store.db');

// Create table
db.exec(`
  CREATE TABLE IF NOT EXISTS products (
    id   INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    data TEXT NOT NULL CHECK(json_valid(data))
  )
`);

// Insert a row
const insert = db.prepare(
  'INSERT INTO products (name, data) VALUES (?, ?)'
);
insert.run('Widget B', JSON.stringify({
  price: 24.99, category: 'electronics', tags: ['premium']
}));

// Query a JSON field
const rows = db
  .prepare(
    "SELECT name, json_extract(data, '$.price') AS price " +
    "FROM products WHERE json_extract(data, '$.category') = ?"
  )
  .all('electronics');
console.log(rows); // [{ name: 'Widget B', price: 24.99 }]

// Expand tags array with json_each
const tags = db
  .prepare(
    'SELECT p.name, e.value AS tag ' +
    'FROM products p, json_each(p.data, \'$.tags\') e'
  )
  .all();
console.log(tags); // [{ name: 'Widget B', tag: 'premium' }]

// Aggregate all products into a JSON array
const summary = db
  .prepare(
    "SELECT json_group_array(json_object('name', name, 'price', " +
    "json_extract(data, '$.price'))) AS result FROM products"
  )
  .get();
console.log(JSON.parse(summary.result));
// [{ name: 'Widget B', price: 24.99 }]

db.close();

When using better-sqlite3, always close the database with db.close() or wrap statements in a transaction for better write performance. For loading bulk JSON files into SQLite, see convert JSON to SQL to generate INSERT statements offline.

Key term definitions

JSON1 extension

The SQLite module that implements all 14 JSON functions. Compiled into the SQLite library by default since version 3.38.0; available as a compile-time option since 3.9.0 (October 2015). No LOAD EXTENSION call is required when using an official SQLite binary.

json_extract

The core read function: json_extract(json, path [, path …]). Returns a native SQLite value at the given JSONPath. When multiple paths are supplied, it returns a JSON array of results. Equivalent to the ->> operator in SQLite 3.38.0+.

json_each

A table-valued function that yields one row per immediate child of a JSON array or object node. Used in the FROM clause with a comma join or JOIN to unnest a JSON column into relational rows without a stored intermediate table.

json_tree

Like json_each but performs a full recursive descent through every node in the JSON document, including nested arrays and objects. Each node appears as a separate row with its fullkey path.

virtual table

A SQLite concept where a function or module presents data as if it were a regular table with rows and columns. json_each and json_tree are virtual tables — they can appear in FROM clauses, support WHERE filtering, and join with real tables, but they have no persistent storage.

expression index

An index defined on the result of an expression rather than a plain column name. SQLite evaluates the expression for each row on write and stores the result in the index B-tree. Queries that use the same expression in a WHERE clause can use the index for O(log n) lookups, enabling fast JSON path queries without changing the table schema.

Frequently asked questions

Does SQLite support JSON natively without extensions?

Yes — since SQLite 3.38.0 (released February 2022) the JSON1 extension is compiled in by default and always available without any LOAD EXTENSION call. On older SQLite builds (3.9.0 through 3.37.x) JSON1 was included in most official binaries as a compile-time option, so it could be absent on stripped-down builds. To confirm that JSON support is active, run SELECT json('{}') — if it returns {} the extension is present; if it raises "no such function: json" you are on a build without JSON1. In practice, all major platforms — Python's sqlite3 module, Node.js better-sqlite3, Android, iOS, macOS, and most Linux distributions — ship with JSON1 enabled. SQLite stores JSON as plain TEXT; there is no separate binary JSONB type unlike PostgreSQL. Validity is enforced by adding a CHECK(json_valid(data)) constraint on the column, which SQLite evaluates on every INSERT and UPDATE. The 14 JSON functions cover reading, modifying, validating, and iterating JSON values entirely within SQL.

How do I query a JSON field in SQLite with json_extract?

Use json_extract(column, path) where path is a JSONPath expression starting with $. The dollar sign represents the root of the document: $.name reads a top-level key, $.address.city reads a nested key, and $.tags[0] reads the first element of a JSON array (zero-indexed). Example: SELECT json_extract(data, '$.price') FROM products WHERE json_extract(data, '$.category') = 'electronics' returns the price for every electronics product. json_extract returns a native SQLite type — TEXT for JSON strings, INTEGER or REAL for numbers, NULL for missing paths, and a JSON string for nested objects or arrays. This means you can use the result directly in arithmetic or string comparisons without casting. SQLite 3.38.0 also added the -> and ->> operators as shorthand: data -> '$.price' returns a JSON value, and data ->> '$.price' returns the unwrapped SQL value, equivalent to json_extract.

How do I iterate over a JSON array in SQLite?

Use json_each(column) in the FROM clause as a virtual table to expand a JSON array stored in a column into one row per element. The virtual table exposes 8 columns: key (integer index for arrays, string key for objects), value (the element value), type (one of null, true, false, integer, real, text, array, object), atom (the primitive value or NULL for nested structures), id, parent, fullkey, and path. Example: SELECT p.name, e.value AS tag FROM products p, json_each(p.data, '$.tags') e returns one row per tag per product. Pass an optional second argument to json_each to start iteration at a sub-path rather than the root. To iterate recursively through all nodes at every nesting level, use json_tree instead of json_each — json_tree descends into nested arrays and objects whereas json_each only walks immediate children. Both functions work with arrays and objects equally well.

Can I index JSON fields in SQLite for faster queries?

Yes — SQLite supports expression indexes, which let you index the result of any deterministic expression including json_extract. The syntax is: CREATE INDEX idx_status ON products(json_extract(data, '$.status')). After creating this index, any WHERE clause that uses exactly the same expression — WHERE json_extract(data, '$.status') = 'active' — will use the index for an O(log n) lookup instead of a full table scan. The expression in the index definition must match the query expression character for character for SQLite's query planner to recognize it. You can also create partial expression indexes to index only a subset of rows: CREATE INDEX idx_active ON products(json_extract(data, '$.price')) WHERE json_extract(data, '$.status') = 'active'. SQLite does not support multi-valued indexes for JSON arrays the way MySQL 8.0.17+ does, so for array membership queries use json_each in a subquery or EXISTS clause. Always run EXPLAIN QUERY PLAN to verify that your index is being used.

What is the difference between SQLite JSON and PostgreSQL JSONB?

The fundamental difference is storage format. SQLite stores JSON as plain TEXT — the raw JSON string is kept exactly as written with no binary encoding, so every json_extract call re-parses the string. PostgreSQL JSONB stores JSON in a decomposed binary format that strips whitespace, reorders keys, and deduplicates object keys on write; reads are faster because no parsing is required. PostgreSQL also has a plain JSON type that stores raw text like SQLite. SQLite has 14 JSON functions covering most common operations; PostgreSQL JSONB has a richer operator set — the @> containment operator, the ? key-exists operator, and GIN indexes that index every key and value in a document. SQLite expression indexes cover point lookups on specific paths; PostgreSQL GIN indexes cover arbitrary key and value searches across the entire document. For lightweight embedded use, mobile apps, or serverless edge deployments, SQLite JSON is sufficient. For analytical workloads or high-write production databases, PostgreSQL JSONB offers better performance. See also MySQL JSON for a comparison with the MySQL approach.

How do I validate that a SQLite column contains valid JSON?

Use the json_valid(value) function, which returns 1 for valid JSON and 0 for invalid input. To enforce validity at the schema level, add a CHECK constraint when creating the table: CREATE TABLE products (id INTEGER PRIMARY KEY, data TEXT NOT NULL CHECK(json_valid(data))). SQLite evaluates the CHECK constraint on every INSERT and UPDATE and raises a "CHECK constraint failed" error if json_valid returns 0 or NULL. Note that json_valid(NULL) returns NULL (not 0), so the constraint is satisfied for NULL values — combine with NOT NULL to disallow both. To audit an existing table for rows with invalid JSON, run: SELECT id FROM products WHERE NOT json_valid(data). Fix or remove those rows before adding the CHECK constraint via a table rebuild. You can also pre-validate data using Jsonic's JSON Validator before it ever reaches the database.