📦

SQLite JSON Functions

SQLite

Complete reference for SQLite JSON functions covering json_extract, json_set, json_insert, json_remove, json_each, json_tree, json_array, json_object, and json_patch. Every function includes syntax, path expression examples, and differences from PostgreSQL JSONB functions. Updated for SQLite 3.52.

32 functions

What are SQLite JSON Functions?

SQLite JSON functions provide full JSON storage and querying support, storing JSON as plain TEXT. json_extract() retrieves values from a JSON string using path expressions (e.g. $.key or $[0]), json_set() updates values, json_each() expands a JSON array into rows, and json_tree() recursively walks the entire JSON structure. Unlike PostgreSQL's JSONB, SQLite JSON has no binary storage format or native indexing — use generated columns with indexes to accelerate JSON queries.

json

SQLite 3.9.0 (2015-10-14); JSON5 support added in SQLite 3.38.0 (2022-02-22) as built-in, 3.42.0 for JSON5 reading→ TEXT

Verifies that its argument is valid JSON (or JSONB) and returns a minified, canonical RFC-8259 text representation with all unnecessary whitespace removed. Converts JSON5 input to canonical JSON. Throws an error if the input is not well-formed.

Signature

json(json)

Parameters

Parameter	Type	Description
json	TEXT or BLOB	A well-formed JSON string, JSON5 string, or JSONB blob. Whitespace is stripped and the result is canonical JSON text.

Examples

Strips whitespace and returns canonical JSON

sql

SELECT json(' { "this" : "is", "a": [ "test" ] } ');

→'{"this":"is","a":["test"]}'

Converts JSON5 unquoted keys to canonical JSON (requires SQLite 3.42.0+)

sql

SELECT json('{x: 42, y: 100}');

→'{"x":42,"y":100}'

Using json() or jsonb() to force a string to be treated as JSON when passing to another function

sql

SELECT json_insert(data, '$.score', jsonb(new_score)) FROM players;

→Updated JSON with score field

Minifies an array by removing spaces

sql

SELECT json('[1, 2,  3]');

→'[1,2,3]'

Useful for normalizing stored JSON before comparison or display

sql

SELECT json(profile) FROM users WHERE id = 1;

→Minified JSON profile string

⚠Anti-Pattern— Using json() to pretty-print output

Developers sometimes call json() expecting formatted, readable output. In reality json() minifies the input — it strips all whitespace and returns the most compact canonical form. Passing pretty-printed JSON through json() produces a single dense line with no newlines or indentation.

✓ Instead: Use json_pretty() when you need human-readable, indented JSON output.

When passing a text string as a VALUE argument to functions like json_insert() or json_object(), SQLite treats it as a literal string by default. Wrap it with json() to make SQLite treat it as actual JSON structure: json_object('data', json('[1,2,3]')) produces {"data":[1,2,3]} rather than {"data":"[1,2,3]"}.

◆SQLite Note

SQLite's json() works on TEXT strings (storing JSON as plain text). This differs from PostgreSQL's JSONB type, which is a binary format. In PostgreSQL, casting to JSON with CAST(x AS JSON) or using json_build_object() is the equivalent. SQLite also understands JSON5 extensions (unquoted keys, trailing commas, comments) since 3.42.0, but always outputs canonical RFC-8259.

jsonb

SQLite 3.45.0 (2024-01-15)→ BLOB

Returns the binary JSONB (internal parse-tree) representation of the input JSON text or JSONB blob. Storing JSON as JSONB avoids re-parsing on every access. If the input is already JSONB, returns a copy without deep validation.

Signature

jsonb(json)

Parameters

Parameter	Type	Description
json	TEXT or BLOB	A well-formed JSON string or JSONB blob. Returns the JSONB binary blob representation.

Examples

Store JSON as binary JSONB for faster subsequent reads

sql

CREATE TABLE events (id INTEGER PRIMARY KEY, payload BLOB);
INSERT INTO events(payload) VALUES (jsonb('{"type":"click","x":100,"y":200}'));

→Row inserted with JSONB blob

Convert stored JSONB back to readable text using json()

sql

SELECT json(payload) FROM events WHERE id = 1;

→'{"type":"click","x":100,"y":200}'

json_extract() accepts JSONB directly — no conversion needed

sql

SELECT json_extract(payload, '$.type') FROM events;

→'click'

Using jsonb_ variants keeps data in binary format throughout the pipeline

sql

UPDATE products SET metadata = jsonb_set(metadata, '$.price', 29.99) WHERE id = 5;

→Updated JSONB blob

Pass jsonb() result into json_insert for more efficient processing

sql

SELECT json_insert(jsonb(data), '$.updated_at', datetime('now')) FROM records;

→Text JSON with added timestamp

⚠Anti-Pattern— Storing jsonb() output in a TEXT column

jsonb() returns a BLOB, not TEXT. If the destination column is declared TEXT, SQLite silently coerces the blob during INSERT. The stored value is no longer valid JSONB binary, so subsequent jsonb_ function calls must re-parse it as text, defeating the entire performance benefit.

✓ Instead: Declare JSONB columns as BLOB (or ANY) so the binary representation is preserved without silent coercion.

When multiple JSON operations occur in sequence, keep data in JSONB throughout the pipeline using jsonb_ variants. Only convert to text at the very end: SELECT json(jsonb_set(jsonb_insert(jsonb(raw), '$.a', 1), '$.b', 2)) avoids two intermediate parse steps. The aggregate functions (json_group_array etc.) are an exception — they actually prefer text inputs.

◆SQLite Note

JSONB in SQLite (added 3.45.0) is SQLite's own internal binary parse-tree format stored as a BLOB. It is NOT compatible with PostgreSQL's JSONB binary format — they share the name but not the format. PostgreSQL JSONB offers O(1) key lookup; SQLite JSONB is O(N) but avoids repeated parsing. Use json_valid(x, 4) to check if a BLOB is JSONB.

json_array

SQLite 3.9.0 (2015-10-14)→ TEXT

Constructs and returns a well-formed JSON array text from zero or more arguments. SQL TEXT values become JSON strings unless they come directly from another JSON function, in which case they are embedded as JSON. NULL becomes JSON null. BLOBs raise an error.

Signatures

json_array()

json_array(value1, value2, ...)

Parameters

Parameter	Type	Description
value	ANY (variadic)	Values to include in the array. TEXT becomes a JSON string. INTEGER/REAL become JSON numbers. NULL becomes JSON null. Results of other JSON functions are embedded as JSON.

Examples

Mix integers and strings; text is quoted

sql

SELECT json_array(1, 2, 'three', 4);

→'[1,2,"three",4]'

Nest arrays by passing json_array() as a value argument

sql

SELECT json_array(1, null, 'hello', json_array(2, 3));

→'[1,null,"hello",[2,3]]'

Build per-row JSON arrays from table columns; use json() to embed stored JSON

sql

SELECT json_array(name, age, json(hobbies)) FROM users WHERE id = 1;

→'["Alice",30,["hiking","reading"]]'

Without json() wrapper the string '[1,2]' would be embedded as a quoted string

sql

SELECT json_array('a', json('[1,2]'));

→'["a",[1,2]]'

Zero arguments returns an empty JSON array

sql

SELECT json_array();

→'[]'

⚠Anti-Pattern— Building JSON arrays with string concatenation

Concatenating strings to form a JSON array — e.g. '["' || col || '"]' — does not escape special characters, quotes, or control characters inside values. A value containing a double-quote or backslash will produce malformed JSON that silently passes string checks but fails later parsing.

✓ Instead: Always use json_array(col) to construct arrays; it handles all escaping automatically and returns guaranteed-valid JSON.

SQLite distinguishes between VALUE arguments (which default to strings) and the output of JSON functions (which are treated as JSON). If you have a column that stores JSON text and want it embedded as actual JSON structure — not a quoted string — wrap it with json(): json_array(json(col)) instead of json_array(col).

◆SQLite Note

Equivalent to PostgreSQL's json_build_array() or ARRAY constructor. In PostgreSQL you can use ARRAY[1,2,'three'] for typed arrays, or json_build_array(1,2,'three') for JSON. SQLite json_array() works entirely on TEXT output. Use jsonb_array() to get BLOB output for chaining with other jsonb_ functions.

jsonb_array

SQLite 3.45.0 (2024-01-15)→ BLOB

Works exactly like json_array() but returns the constructed JSON array as a binary JSONB blob instead of text. Use when the result will be passed to other jsonb_ functions or stored in a JSONB column for performance.

Signatures

jsonb_array()

jsonb_array(value1, value2, ...)

Parameters

Parameter	Type	Description
value	ANY (variadic)	Values to include in the array. Same coercion rules as json_array(). Returns BLOB instead of TEXT.

Examples

Returns a BLOB — not human-readable; use json() to convert back to text

sql

SELECT jsonb_array(1, 2, 3);

→BLOB (binary JSONB representing [1,2,3])

Wrap with json() to get readable text output

sql

SELECT json(jsonb_array(1, 2, 3));

→'[1,2,3]'

Store as JSONB for faster subsequent json_extract calls

sql

INSERT INTO log(tags) VALUES (jsonb_array('error', 'auth', 'timeout'));

→Row inserted with JSONB array blob

Passing jsonb_array() result as a VALUE is more efficient than json_array()

sql

SELECT json_set(doc, '$.tags', jsonb_array('a','b','c')) FROM docs;

→Updated JSON text with embedded array

All json_ functions accept JSONB input transparently

sql

SELECT json_array_length(jsonb_array(10, 20, 30, 40));

→4

⚠Anti-Pattern— Storing jsonb_array() result in a TEXT column

jsonb_array() returns a BLOB. Inserting or assigning this BLOB into a TEXT column causes SQLite to silently convert it to a text representation that is NOT valid JSONB. All subsequent jsonb_ calls on that column then fall back to text-path processing with no error.

✓ Instead: Always store jsonb_array() results in a BLOB-typed column to preserve the binary format.

When building a JSON structure with multiple nested function calls, use jsonb_ variants for intermediate results and only call json() at the outermost level. This avoids repeated text parsing at each step: json(jsonb_set(jsonb_insert(jsonb_array(...), ...), ...)).

◆SQLite Note

No direct PostgreSQL equivalent — PostgreSQL's json_build_array() always returns text JSON. JSONB in SQLite is a performance optimization at the function-call level. Store JSONB blobs in columns declared as BLOB or ANY (not TEXT) to avoid accidental TEXT coercion.

json_array_length

SQLite 3.9.0 (2015-10-14)→ INTEGER

Returns the number of elements in the top-level JSON array, or in the array located at the given path. Returns 0 if the target is not a JSON array. Returns NULL if the path does not exist. Throws an error for malformed JSON.

Signatures

json_array_length(json)

json_array_length(json, path)

Parameters

Parameter	Type	Description
json	TEXT or BLOB	A well-formed JSON string or JSONB blob.
path	TEXT	Optional JSONPath expression (e.g., '$.items'). If omitted, the top-level element is used.

Examples

Count elements in a top-level array

sql

SELECT json_array_length('[1,2,3,4,5]');

→5

Count items in a nested array within a stored JSON column

sql

SELECT json_array_length(cart, '$.items') FROM orders WHERE id = 42;

→3

Filter rows based on array length

sql

SELECT name FROM users WHERE json_array_length(phone_numbers) > 1;

→Users with more than one phone number

Returns 0 for a JSON object (not an array)

sql

SELECT json_array_length('{"a":1}');

→0

Returns 0 because element at index 1 (value 2) is not an array

sql

SELECT json_array_length('[1,2,3]', '$[1]');

→0

⚠Anti-Pattern— Calling json_array_length on a JSON object and expecting an error

When passed a JSON object, json_array_length() silently returns 0, not an error or NULL. A WHERE clause like WHERE json_array_length(col) > 0 will silently exclude both empty arrays and all objects, losing rows without any indication that the wrong type was encountered.

✓ Instead: Check json_type(col) = 'array' before calling json_array_length() so objects and non-arrays are handled explicitly.

json_array_length() returns NULL when the path doesn't exist, and 0 when the path exists but points to a non-array. This distinction matters in WHERE clauses: WHERE json_array_length(col, '$.tags') > 0 will only include rows where '$.tags' exists AND is a non-empty array, silently skipping rows where '$.tags' is absent.

◆SQLite Note

Equivalent to PostgreSQL's jsonb_array_length(jsonb) or json_array_length(json). PostgreSQL requires the value to actually be an array and throws an error otherwise. SQLite returns 0 for non-arrays. PostgreSQL uses -> operator for path navigation; SQLite uses a path argument string.

json_error_position

SQLite 3.42.0 (2023-05-16)→ INTEGER

Returns 0 if the input is valid JSON or JSON5 text, or valid JSONB. If the input contains a syntax error, returns the 1-based character position of the first error in text, or an approximate 1-based byte position for BLOB inputs. Unlike most JSON functions, this does NOT throw an error on bad input.

Signature

json_error_position(json)

Parameters

Parameter	Type	Description
json	TEXT or BLOB	Any text or BLOB to check for JSON validity. Does not throw on malformed input — that is the point of this function.

Examples

0 means the JSON is valid

sql

SELECT json_error_position('{"name":"Alice","age":30}');

→0

Missing comma; error at character 17

sql

SELECT json_error_position('{"name":"Alice" "age":30}');

→17

Audit table for malformed JSON — find and report bad rows

sql

SELECT raw_input, json_error_position(raw_input) AS err_pos
FROM incoming_data
WHERE json_error_position(raw_input) != 0;

→Rows with invalid JSON and error positions

Safe extraction that gracefully handles corrupt data

sql

SELECT CASE WHEN json_error_position(payload) = 0
       THEN json_extract(payload, '$.event')
       ELSE 'INVALID' END AS event
FROM events;

→Event name or 'INVALID'

Extract context around the error for debugging

sql

SELECT substr(raw, json_error_position(raw) - 5, 20) AS context
FROM import_queue
WHERE json_error_position(raw) > 0;

→20-character snippet around the error location

⚠Anti-Pattern— Treating the returned position as an exact character offset for BLOB inputs

For TEXT inputs json_error_position() returns a reliable 1-based character position. For BLOB (JSONB) inputs the returned value is an approximate byte offset into the binary format, not a character position in the original JSON source. Using that offset to slice the original source string produces misleading context.

✓ Instead: For BLOB inputs, first convert to text with json() and then run json_error_position() on the text form to get a meaningful character position.

Most JSON functions throw errors on bad input — json_error_position() is the exception. Use it as a guard before calling other JSON functions on untrusted input. A value of 0 means safe to proceed. This is particularly useful in ETL pipelines where you want to log and skip bad rows rather than abort the entire query.

◆SQLite Note

No direct equivalent in PostgreSQL. PostgreSQL typically raises an exception on malformed JSON; you'd use a PL/pgSQL block with EXCEPTION to catch errors. SQLite's json_error_position() provides a non-throwing alternative specifically for validation. Added in SQLite 3.42.0 — earlier versions need json_valid() which only returns 0/1 without a position.

json_extract

SQLite 3.9.0 (2015-10-14); ->> operator requires SQLite 3.38.0 (2022-02-22)→ ANY

Extracts and returns one or more values from a JSON string or JSONB blob. With a single path, returns the native SQL type: NULL for JSON null, INTEGER/REAL for numbers, TEXT for strings, and TEXT JSON for arrays/objects. With multiple paths, always returns a JSON array of results.

Signatures

json_extract(json, path)

json_extract(json, path1, path2, ...)

Parameters

Parameter	Type	Description
json	TEXT or BLOB	A well-formed JSON string or JSONB blob.
path	TEXT	A JSONPath expression starting with $. Use $ for the whole document, $.key for an object field, $[N] for array index (0-based), $[#-1] for last element.

Examples

Extract a string — returns native SQL TEXT (no quotes)

sql

SELECT json_extract('{"name":"Alice","age":30}', '$.name');

→'Alice'

Extract first array element — returns INTEGER

sql

SELECT json_extract('{"scores":[95,87,92]}', '$.scores[0]');

→95

Nested path navigation using dot notation

sql

SELECT json_extract(data, '$.address.city') FROM users WHERE id = 1;

→'Portland'

#-1 accesses the last element of an array

sql

SELECT json_extract(data, '$.tags[#-1]') FROM posts;

→Last tag string

Multiple paths return a JSON array of values

sql

SELECT json_extract(info, '$.name', '$.email') FROM contacts;

→'["Alice","alice@example.com"]'

⚠Anti-Pattern— Expecting individual scalar values when passing multiple paths

With a single path, json_extract() returns a native SQL scalar. With two or more paths it always returns a JSON array text — even if only one of those paths was added dynamically. Code that branches on the return type will behave differently once a second path is supplied, causing silent type mismatches.

✓ Instead: Call json_extract() once per path when you need individual native-typed values; use the multi-path form only when a JSON array result is explicitly desired.

Since SQLite 3.38.0, col ->> '$.key' is shorthand for json_extract(col, '$.key') and always returns the SQL native type (like json_extract with a single path). The -> operator returns JSON representation instead. You can also chain: data -> '$.address' ->> 'city'. This syntax is compatible with PostgreSQL's JSONB operators.

◆SQLite Note

Comparable to PostgreSQL's jsonb_extract_path() or the ->> operator. Key difference: SQLite json_extract() with a single scalar path returns a native SQL type (TEXT for strings, INTEGER for numbers), whereas MySQL's json_extract() always returns JSON. PostgreSQL ->> returns TEXT. Use jsonb_extract() instead to get JSONB blobs for array/object results. SQLite paths start with $ and use dot/bracket notation similar to PostgreSQL's jsonpath.

jsonb_extract

SQLite 3.45.0 (2024-01-15)→ ANY (BLOB for arrays/objects, native type for primitives)

Works the same as json_extract() except that when the result would be a JSON array or object, it returns it as a JSONB blob instead of text JSON. For primitive values (string, number, null, boolean), the behavior is identical to json_extract().

Signatures

jsonb_extract(json, path)

jsonb_extract(json, path1, path2, ...)

Parameters

Parameter	Type	Description
json	TEXT or BLOB	A well-formed JSON string or JSONB blob.
path	TEXT	JSONPath expression. Same syntax as json_extract().

Examples

Extract a nested object as JSONB, then convert to text for display

sql

SELECT json(jsonb_extract(data, '$.address')) FROM users WHERE id = 1;

→'{"street":"123 Main","city":"Portland"}'

Returns JSONB blob when extracting an array — ready for chaining

sql

SELECT jsonb_extract(payload, '$.items') FROM orders WHERE id = 5;

→BLOB (JSONB array)

Chain directly — json_array_length() accepts JSONB input

sql

SELECT json_array_length(jsonb_extract(data, '$.tags')) FROM posts;

→Integer count

Primitive values return native SQL types just like json_extract()

sql

SELECT jsonb_extract(data, '$.score') FROM players;

→95 (INTEGER)

Extract entire document as JSONB then modify — avoids double parse

sql

UPDATE docs
SET metadata = jsonb_set(jsonb_extract(metadata, '
    
  

), '$.version', 2)
WHERE id = 1;

→Updated JSONB metadata

⚠Anti-Pattern— Using jsonb_extract() when the column is stored as TEXT

jsonb_extract() expects BLOB input for peak efficiency. When the column is TEXT (because JSONB was accidentally stored with TEXT affinity), SQLite must re-parse the text on every call, giving the same performance as json_extract() but with added confusion. There is no error — it silently works slower.

✓ Instead: Ensure JSONB columns are declared BLOB so jsonb_extract() receives a true BLOB and skips the text-parsing step entirely.

If you extract a sub-document and immediately pass it to another JSON function, jsonb_extract() avoids the TEXT→JSONB parse step that json_extract() would require. The performance benefit is largest when the extracted sub-document is complex or large.

◆SQLite Note

No direct PostgreSQL equivalent by this name. PostgreSQL's jsonb -> 'key' operator returns JSONB when key exists. In SQLite, jsonb_extract() is the JSONB-returning counterpart to json_extract(). Only available since 3.45.0. For compatibility with older SQLite, fall back to json_extract().

json_insert

SQLite 3.9.0 (2015-10-14)→ TEXT

Inserts new values into a JSON document. Only creates new keys/elements — does NOT overwrite existing values. To append to an array, use the special path '$[#]'. Multiple path/value pairs are applied left-to-right.

Signatures

json_insert(json, path, value)

json_insert(json, path1, value1, path2, value2, ...)

Parameters

Parameter	Type	Description
json	TEXT or BLOB	The base JSON document to modify.
path	TEXT	JSONPath to the insertion point. Use '$[#]' to append to an array.
value	ANY	The value to insert. TEXT is quoted as a JSON string unless it comes from another JSON function or -> operator.

Examples

Insert a new key; existing key 'a' is unchanged

sql

SELECT json_insert('{"a":1}', '$.b', 2);

→'{"a":1,"b":2}'

No-op: '$.a' already exists, so insert does nothing

sql

SELECT json_insert('{"a":1,"b":2}', '$.a', 99);

→'{"a":1,"b":2}'

Append to end of array using '$[#]'

sql

SELECT json_insert('[1,2,3]', '$[#]', 4);

→'[1,2,3,4]'

Safe idempotent insert — will not overwrite an existing timestamp

sql

UPDATE events SET data = json_insert(data, '$.processed_at', datetime('now')) WHERE processed_at IS NULL;

→Adds processed_at timestamp only if not already present

Insert multiple keys at once using nested json_object()

sql

SELECT json_insert(settings, '$.theme', 'dark', '$.notifications', json_object('email', 1, 'sms', 0)) FROM user_prefs WHERE id = 1;

→JSON with new theme and notifications keys added

⚠Anti-Pattern— Using json_insert to update an existing key

json_insert() silently ignores any path that already exists in the document. If used expecting upsert behavior — intending to update an existing field — the existing value is left unchanged and no error or warning is raised. This is a common source of bugs when switching from json_set().

✓ Instead: Use json_set() for upsert semantics; reserve json_insert() only when you explicitly want to protect existing values from overwrite.

SQLite has three modification functions with different upsert semantics: json_insert() creates new keys but never overwrites existing ones; json_replace() updates existing keys but never creates new ones; json_set() does both (upsert). Choose based on your intent to avoid accidental overwrites or silent no-ops.

◆SQLite Note

In PostgreSQL you would use jsonb_set() which always upserts, or the || (concatenation) operator for merging. There is no direct 'insert only' equivalent in PostgreSQL JSONB. SQLite's distinction between insert/replace/set provides finer control. The jsonb_insert() variant returns JSONB blob instead of TEXT.

jsonb_insert

SQLite 3.45.0 (2024-01-15)→ BLOB

Works identically to json_insert() but returns the modified JSON document as a binary JSONB blob instead of text. Only inserts at paths that do not already exist.

Signatures

jsonb_insert(json, path, value)

jsonb_insert(json, path1, value1, path2, value2, ...)

Parameters

Parameter	Type	Description
json	TEXT or BLOB	The base JSON document (text or JSONB).
path	TEXT	JSONPath to the insertion point.
value	ANY	Value to insert. Same coercion rules as json_insert().

Examples

Returns BLOB; wrap with json() to read as text

sql

SELECT json(jsonb_insert('{"a":1}', '$.b', 2));

→'{"a":1,"b":2}'

Add a security token to existing sessions only if not present

sql

UPDATE sessions SET state = jsonb_insert(state, '$.csrf_token', hex(randomblob(16))) WHERE expires > datetime('now');

→JSONB state blob updated with new CSRF token

Chain jsonb_ functions for multi-step updates in BLOB format

sql

SELECT jsonb_set(jsonb_insert(data, '$.step1', 'done'), '$.step2', 'pending') FROM workflows;

→JSONB with both fields set

Append to array; JSONB output

sql

SELECT json(jsonb_insert('[10,20,30]', '$[#]', 40));

→'[10,20,30,40]'

Insert and immediately query the result without text conversion

sql

SELECT json_array_length(jsonb_insert(tags, '$[#]', 'new-tag')) FROM articles WHERE id = 1;

→Length of updated tag array

⚠Anti-Pattern— Expecting jsonb_insert to overwrite existing keys

Like json_insert(), jsonb_insert() only adds new keys and silently does nothing for paths that already exist. Developers migrating from jsonb_set() sometimes switch to jsonb_insert() and then observe that updates stop happening in production without any error, because the key was already present.

✓ Instead: Use jsonb_set() for upsert behavior; use jsonb_insert() only when the intent is strictly to add absent keys.

If your table stores JSON in a BLOB column (as JSONB), use jsonb_insert() instead of json_insert() to avoid a TEXT→BLOB round-trip on the UPDATE. The stored JSONB is parsed, modified, and the result is written back as JSONB without ever converting to text.

◆SQLite Note

PostgreSQL does not have a separate insert-only JSON function. This is SQLite-specific. In PostgreSQL, jsonb_set() with create_missing=true is the upsert, and there is no native 'insert only if absent' for JSONB keys outside of custom logic.

json_object

SQLite 3.9.0 (2015-10-14)→ TEXT

Constructs a well-formed JSON object from alternating label/value pairs. Labels must be TEXT. Values follow standard JSON value coercion: SQL TEXT becomes a JSON string unless it comes from a JSON function. Returns an empty object '{}' with no arguments.

Signatures

json_object()

json_object(label1, value1, label2, value2, ...)

Parameters

Parameter	Type	Description
label	TEXT	Key name for the JSON object property.
value	ANY	Value for the property. TEXT is quoted unless it comes from another JSON function or -> operator.

Examples

Build a simple JSON object from literals

sql

SELECT json_object('name', 'Alice', 'age', 30, 'active', 1);

→'{"name":"Alice","age":30,"active":1}'

Serialize a row to JSON

sql

SELECT json_object('id', id, 'name', name, 'email', email) FROM users WHERE id = 1;

→'{"id":1,"name":"Alice","email":"alice@example.com"}'

Nest json_object() calls to build hierarchical JSON

sql

SELECT json_object('user', json_object('name', name, 'age', age), 'score', score) FROM players;

→'{"user":{"name":"Bob","age":25},"score":1500}'

Combine with json_array() for rich nested structures

sql

SELECT json_object('tags', json_array('sql', 'sqlite', 'json'), 'meta', json_object('version', 3));

→'{"tags":["sql","sqlite","json"],"meta":{"version":3}}'

Without json() wrapper, the string is embedded as a literal string, not an array

sql

SELECT json_object('data', '[1,2,3]');

→'{"data":"[1,2,3]"}'

⚠Anti-Pattern— Embedding a JSON text column directly without wrapping it in json()

If a column holds JSON text (e.g., '[1,2,3]') and you pass it directly to json_object() as a value, SQLite treats it as a plain SQL string and double-quotes it in the output. The resulting object looks correct in a text dump but contains a JSON string literal where a structured value was expected.

✓ Instead: Wrap JSON-valued column references with json(): json_object('tags', json(col)) to embed them as actual JSON structures rather than string literals.

Combine json_object() with column references to produce JSON row representations without a custom serializer: SELECT json_object('id', id, 'name', name) FROM users. For arrays of rows, wrap with json_group_array(): SELECT json_group_array(json_object('id', id, 'name', name)) FROM users.

◆SQLite Note

Equivalent to PostgreSQL's json_build_object() or jsonb_build_object(). Syntax is identical: alternating key/value pairs. PostgreSQL also has row_to_json() which converts an entire row. SQLite has no row_to_json() but json_object() with explicit columns achieves the same result. Use jsonb_object() for BLOB output.

jsonb_object

SQLite 3.45.0 (2024-01-15)→ BLOB

Works identically to json_object() but returns the constructed JSON object as a binary JSONB blob instead of text. Use when the object will be stored in a JSONB column or passed to other jsonb_ functions.

Signatures

jsonb_object()

jsonb_object(label1, value1, label2, value2, ...)

Parameters

Parameter	Type	Description
label	TEXT	Key name for the JSON object property.
value	ANY	Value for the property. Same coercion rules as json_object().

Examples

Store session data as JSONB from the start

sql

INSERT INTO sessions(data) VALUES (jsonb_object('user_id', 42, 'created', datetime('now'), 'active', 1));

→Row inserted with JSONB session blob

Readable text form using json() wrapper

sql

SELECT json(jsonb_object('a', 1, 'b', 2));

→'{"a":1,"b":2}'

Chain jsonb_ functions without TEXT conversion

sql

SELECT jsonb_set(jsonb_object('x', 10), '$.y', 20);

→JSONB blob representing {"x":10,"y":20}

json_extract() accepts JSONB directly

sql

SELECT json_extract(jsonb_object('name', 'Alice', 'scores', json_array(90, 85)), '$.scores[0]');

→90

Replace entire settings object with a fresh JSONB blob

sql

UPDATE config SET settings = jsonb_object('theme', 'dark', 'lang', 'en', 'tz', 'UTC') WHERE user_id = 1;

→Updated JSONB settings

⚠Anti-Pattern— Immediately casting jsonb_object() output to TEXT for storage

The whole benefit of jsonb_object() is producing a BLOB that future jsonb_ reads and writes can use without re-parsing. If the result is immediately coerced to TEXT — by column affinity, CAST, or concatenation — the binary representation is lost and the extra cost of jsonb_object() over json_object() was wasted.

✓ Instead: Store jsonb_object() output in a BLOB column and only call json() for display or export at the final step.

When inserting many rows with JSON columns, constructing each row with jsonb_object() instead of json_object() avoids the TEXT→JSONB parse on read. The write is slightly more expensive but all subsequent reads and modifications are faster.

◆SQLite Note

Equivalent to PostgreSQL's jsonb_build_object() in intent, but returns SQLite's own internal JSONB binary format (not PostgreSQL's JSONB format). Not binary-compatible across databases.

json_patch

SQLite 3.9.0 (2015-10-14)→ TEXT

Applies an RFC 7396 JSON Merge Patch to the target JSON document. Merge Patch can add, modify, or delete fields in a JSON object. Setting a key to JSON null deletes that key. Arrays are treated as atomic values — you cannot append or modify individual array elements with a patch.

Signature

json_patch(target, patch)

Parameters

Parameter	Type	Description
target	TEXT or BLOB	The base JSON document to patch.
patch	TEXT or BLOB	An RFC 7396 merge patch document. Keys present with non-null values are set/updated; keys with null values are deleted; keys absent from patch are left unchanged.

Examples

Add new fields from the patch

sql

SELECT json_patch('{"a":1,"b":2}', '{"c":3,"d":4}');

→'{"a":1,"b":2,"c":3,"d":4}'

Update 'a', delete 'b' (null means delete), add 'c'

sql

SELECT json_patch('{"a":1,"b":2}', '{"a":9,"b":null,"c":8}');

→'{"a":9,"c":8}'

Deep merge for nested objects

sql

SELECT json_patch('{"a":{"x":1,"y":2},"b":3}', '{"a":{"y":9},"c":8}');

→'{"a":{"x":1,"y":9},"b":3,"c":8}'

Common pattern: partial update via API sends only changed fields as the patch

sql

UPDATE user_prefs SET settings = json_patch(settings, ?) WHERE user_id = ?;

→Settings updated with partial changes

Arrays are replaced atomically — cannot append with json_patch; use json_insert for array append

sql

SELECT json_patch('{"tags":["sql","db"]}', '{"tags":["nosql"]}');

→'{"tags":["nosql"]}'

⚠Anti-Pattern— Using json_patch to delete keys by setting them to null — then being surprised

RFC 7396 merge patch defines keys set to null in the patch document as deletions, not as setting the target key to JSON null. This means json_patch(doc, '{"key":null}') removes "key" entirely from the output. Developers expecting the key to remain present with a null value find it gone silently.

✓ Instead: Use json_patch for field deletion intentionally; to set a key to JSON null while keeping it present, use json_set(doc, '$.key', null) instead.

When a REST API sends partial updates (PATCH requests), json_patch() maps directly to RFC 7396 semantics: the payload is the patch. Absent fields are preserved, present fields are updated, and fields explicitly set to null are deleted. For array modifications (append, update element), use json_insert() or json_set() instead.

◆SQLite Note

No direct equivalent in PostgreSQL by this name. PostgreSQL uses the || (concatenation/merge) operator for shallow merges: '{"a":1}' || '{"b":2}'. For deep nested merges, PostgreSQL requires custom functions. Note that RFC 7396 (json_patch) is a shallow merge for top-level keys but recursively merges nested objects — it does NOT deep-clone nested objects on unchanged paths.

jsonb_patch

SQLite 3.45.0 (2024-01-15)→ BLOB

Works identically to json_patch() but returns the patched document as a binary JSONB blob instead of text JSON.

Signature

jsonb_patch(target, patch)

Parameters

Parameter	Type	Description
target	TEXT or BLOB	The base JSON document (text or JSONB) to patch.
patch	TEXT or BLOB	An RFC 7396 merge patch document (text or JSONB).

Examples

Apply patch, return JSONB, convert to text for display

sql

SELECT json(jsonb_patch('{"a":1}', '{"b":2}'));

→'{"a":1,"b":2}'

Update stored JSONB column with a patch; stays as JSONB

sql

UPDATE documents SET body = jsonb_patch(body, ?) WHERE doc_id = ?;

→JSONB body updated in-place

Chain jsonb_ operations without TEXT conversion

sql

SELECT jsonb_set(jsonb_patch(data, '{"status":"active"}'), '$.updated_at', datetime('now')) FROM items;

→JSONB with status updated and timestamp added

Apply patch and immediately extract a field

sql

SELECT json_extract(jsonb_patch(profile, delta), '$.name') FROM user_updates;

→Updated name after applying delta patch

Merge per-user overrides onto global defaults

sql

SELECT jsonb_patch(defaults, overrides) FROM config WHERE scope = 'global';

→JSONB blob with overrides applied to defaults

⚠Anti-Pattern— Chaining jsonb_patch on a TEXT column expecting the JSONB output to be preserved

jsonb_patch() returns a BLOB, but if the target column has TEXT affinity the UPDATE silently coerces the BLOB back to TEXT. On the next call to jsonb_patch() the input is TEXT again and must be re-parsed, negating all performance gains with no error to indicate the problem.

✓ Instead: Confirm the storage column is declared BLOB or ANY before using jsonb_patch() in UPDATE statements so the JSONB result is preserved across writes.

If both the stored document and incoming patch are in JSONB format, jsonb_patch() requires no text parsing at all. This is the fastest possible update path for partial JSON document updates in SQLite.

◆SQLite Note

JSONB-returning variant of json_patch(). No PostgreSQL equivalent. PostgreSQL's || merge operator on JSONB is shallow. SQLite's json_patch/jsonb_patch implement RFC 7396 which does recursive object merging.

json_pretty

SQLite 3.46.0 (2024-05-23)→ TEXT

Returns the JSON document formatted with indentation and newlines for human readability. Default indentation is 4 spaces per level. The optional second argument specifies a custom indentation string (e.g., 2 spaces, or a tab character).

Signatures

json_pretty(json)

json_pretty(json, indent)

Parameters

Parameter	Type	Description
json	TEXT or BLOB	A well-formed JSON string or JSONB blob to format.
indent	TEXT	Optional. Indentation string per nesting level. Defaults to 4 spaces if omitted or NULL.

Examples

Pretty-print with default 4-space indentation

sql

SELECT json_pretty('{"name":"Alice","scores":[95,87,92]}');

→

{
    "name": "Alice",
    "scores": [
        95,
        87,
        92
    ]
}

Use 2-space indentation for more compact output

sql

SELECT json_pretty(data, '  ') FROM config WHERE id = 1;

→2-space indented JSON

Use a tab character for indentation

sql

SELECT json_pretty(data, char(9)) FROM config WHERE id = 1;

→Tab-indented JSON

Useful in SQLite CLI for inspecting complex JSON blobs

sql

.mode line
SELECT json_pretty(settings) AS formatted FROM user_prefs LIMIT 1;

→Human-readable multi-line JSON settings

Compare minified vs pretty-printed size

sql

SELECT length(json(data)) AS minified_len, length(json_pretty(data)) AS pretty_len FROM documents LIMIT 5;

→Side-by-side byte count comparison

⚠Anti-Pattern— Storing json_pretty() output persistently in the database

json_pretty() is intended for display and debugging. Storing pretty-printed JSON in a table column inflates storage by 20–40% depending on nesting depth, slows full-table scans, and provides no functional benefit over minified JSON since all json_ functions handle both forms equally.

✓ Instead: Store JSON in its minified form using json() or jsonb(); call json_pretty() only in the SELECT list for display or in application-layer logging.

json_pretty() is ideal for development, logging, and user-facing output where readability matters. Always store JSON in its minified form (using json() or jsonb()) to reduce storage and improve scan performance. Switch to json_pretty() only at the presentation layer.

◆SQLite Note

Similar to PostgreSQL's jsonb_pretty(jsonb) function which also formats JSONB as indented text. PostgreSQL jsonb_pretty() does not take an indent argument (always 4 spaces). SQLite json_pretty() adds the flexible indent parameter. Both are primarily debug/display utilities.

json_quote

SQLite 3.9.0 (2015-10-14)→ TEXT

Converts an SQL value to its JSON representation. Strings are double-quoted and escaped; numbers are returned as-is; NULL becomes the JSON null literal. If the argument is already the result of a JSON function, it is returned unchanged (no-op).

Signature

json_quote(value)

Parameters

Parameter	Type	Description
value	ANY	An SQL value (TEXT, INTEGER, REAL, or NULL) to convert to a JSON representation.

Examples

Wraps the string in JSON double-quotes

sql

SELECT json_quote('hello world');

→'"hello world"'

Numbers pass through unchanged

sql

SELECT json_quote(3.14159);

→3.14159

SQL NULL becomes the JSON string 'null'

sql

SELECT json_quote(NULL);

→'null'

Special characters are properly escaped in the JSON output

sql

SELECT json_quote('It''s a "test"');

→'"It''s a \"test\""'

A JSON-looking string is escaped, not parsed — use json('[1,2,3]') if you want actual JSON

sql

SELECT json_quote('[1,2,3]');

→'"[1,2,3]"'

⚠Anti-Pattern— Using json_quote() to serialize objects or arrays

json_quote() converts a single SQL scalar into a JSON string literal — it does not serialize arrays or objects. Passing a TEXT string that looks like JSON (e.g., '[1,2,3]') through json_quote() double-quotes and escapes it, producing '"[1,2,3]"' — a JSON string, not an array.

✓ Instead: Use json_object() or json_array() to construct JSON objects and arrays; use json_quote() only when you specifically need to wrap a scalar SQL value as a JSON string literal.

When manually constructing JSON via string concatenation (which you should generally avoid), json_quote() correctly handles escaping. But prefer json_object() or json_array() which do not require manual escaping and are safer. json_quote() is most useful for understanding what SQLite considers a valid JSON-encoded value.

◆SQLite Note

Similar in purpose to PostgreSQL's to_json() or to_jsonb() functions. PostgreSQL's to_json(val) converts any SQL value (including composite types and arrays) to JSON. SQLite json_quote() only handles scalars. For complex type serialization in SQLite, use json_object() or json_array().

json_remove

SQLite 3.9.0 (2015-10-14)→ TEXT

Returns a copy of the JSON document with all elements identified by path arguments removed. Paths that do not exist are silently ignored. Removals are applied left to right, so earlier removals can affect later paths. Called with no path arguments, reformats the JSON (removes whitespace).

Signatures

json_remove(json)

json_remove(json, path)

json_remove(json, path1, path2, ...)

Parameters

Parameter	Type	Description
json	TEXT or BLOB	The base JSON document.
path	TEXT (variadic)	JSONPath expression(s) identifying element(s) to remove. Non-existent paths are silently ignored.

Examples

Remove a single object key

sql

SELECT json_remove('{"a":1,"b":2,"c":3}', '$.b');

→'{"a":1,"c":3}'

Remove array element at index 2

sql

SELECT json_remove('[0,1,2,3,4]', '$[2]');

→'[0,1,3,4]'

Strip sensitive fields before returning data to an API

sql

SELECT json_remove(data, '$.password', '$.ssn', '$.credit_card') FROM users WHERE id = 1;

→User JSON with sensitive fields stripped

Remove last then first element (order matters; indices shift after each removal)

sql

SELECT json_remove('[0,1,2,3,4]', '$[#-1]', '$[0]');

→'[1,2,3]'

Periodic cleanup of internal metadata from old records

sql

UPDATE audit_log SET entry = json_remove(entry, '$.internal_id', '$.debug_info') WHERE created < date('now','-30 days');

→Old log entries with internal fields pruned

⚠Anti-Pattern— Removing a nonexistent path and expecting an error or NULL return

json_remove() silently returns the unchanged document when the specified path does not exist. There is no NULL result, no 0 rows, and no error. Code that checks the return value to confirm a field was removed will always see a non-null result and incorrectly infer success.

✓ Instead: Check json_type(col, '$.key') IS NOT NULL before calling json_remove() if you need to confirm the target path actually existed.

When removing multiple array elements, remember that each removal shifts indices. Removing '$[0]' and then '$[2]' removes what was originally at index 0 and then what was originally at index 3 (since everything shifts left). To remove by stable positions, work from the end: remove higher indices first, or use '#-N' notation.

◆SQLite Note

Equivalent to PostgreSQL's jsonb_delete(jsonb, text) for key deletion or jsonb_delete_path(jsonb, text[]) for path deletion. PostgreSQL also supports the - operator: data - 'key' removes a top-level key. SQLite json_remove() supports full path syntax including array indices. Use jsonb_remove() for BLOB output.

jsonb_remove

SQLite 3.45.0 (2024-01-15)→ BLOB

Works identically to json_remove() but returns the modified document as a binary JSONB blob instead of text.

Signatures

jsonb_remove(json)

jsonb_remove(json, path)

jsonb_remove(json, path1, path2, ...)

Parameters

Parameter	Type	Description
json	TEXT or BLOB	The base JSON document (text or JSONB).
path	TEXT (variadic)	JSONPath expression(s) of elements to remove.

Examples

Remove key and display as text

sql

SELECT json(jsonb_remove('{"a":1,"b":2}', '$.b'));

→'{"a":1}'

Clean up fields on publish; stays in JSONB format

sql

UPDATE documents SET data = jsonb_remove(data, '$.draft', '$.review_notes') WHERE published = 1;

→Published docs with draft fields removed, stored as JSONB

Rename a field by removing the old and setting the new in one chain

sql

SELECT jsonb_set(jsonb_remove(profile, '$.old_address'), '$.address', new_address) FROM user_migration;

→JSONB with old field removed and new field added

Remove and immediately query the result

sql

SELECT json_extract(jsonb_remove(config, '$.deprecated_options'), '$.version') FROM app_config;

→Version number after removing deprecated options

Remove first tag and count remaining

sql

SELECT json_array_length(jsonb_remove(tags, '$[0]')) FROM posts WHERE id = 1;

→Tag count after removing first tag

⚠Anti-Pattern— Assigning jsonb_remove() output to a TEXT column and losing the BLOB

jsonb_remove() returns a BLOB. If the result is assigned to a TEXT column — by column affinity or implicit coercion — the binary JSONB structure is silently converted to text. All subsequent jsonb_ calls on that column must then re-parse from text, and no error indicates the round-trip occurred.

✓ Instead: Always target a BLOB column when assigning jsonb_remove() output; verify column affinity with PRAGMA table_info before deploying.

For complex document transformations involving multiple remove and set operations, keeping the data in JSONB throughout the chain avoids repeated text parsing: jsonb_set(jsonb_insert(jsonb_remove(data, '$.old'), '$.new', val), '$.ts', datetime('now')).

◆SQLite Note

No direct PostgreSQL counterpart by this name. PostgreSQL's - operator on JSONB (jsonb - text) removes a top-level key and returns JSONB. For path-based deletion, PostgreSQL uses #- operator. SQLite's jsonb_remove() supports full JSONPath with nested paths and array indices.

json_replace

SQLite 3.9.0 (2015-10-14)→ TEXT

Updates existing values in a JSON document. Only updates keys/elements that already exist — does NOT create new keys. Multiple path/value pairs are applied left to right. Returns the modified JSON text.

Signatures

json_replace(json, path, value)

json_replace(json, path1, value1, path2, value2, ...)

Parameters

Parameter	Type	Description
json	TEXT or BLOB	The base JSON document.
path	TEXT	JSONPath to the element to replace.
value	ANY	The replacement value. TEXT is quoted as JSON string unless from a JSON function or -> operator.

Examples

Replace an existing key

sql

SELECT json_replace('{"a":1,"b":2}', '$.a', 99);

→'{"a":99,"b":2}'

No-op: '$.c' does not exist, so nothing is changed

sql

SELECT json_replace('{"a":1,"b":2}', '$.c', 99);

→'{"a":1,"b":2}'

Update only fields that are expected to exist; safely skips if missing

sql

UPDATE products SET data = json_replace(data, '$.price', new_price, '$.updated_at', datetime('now')) WHERE sku = ?;

→Product JSON with price and timestamp updated

Increment an existing value using extract + replace

sql

SELECT json_replace(scores, '$.math', json_extract(scores,'$.math') + 5) FROM students WHERE id = 1;

→Scores with math incremented by 5

Replace an array element by index

sql

SELECT json_replace('[10,20,30]', '$[1]', 99);

→'[10,99,30]'

⚠Anti-Pattern— Using json_replace to add a new key when it does not yet exist

json_replace() silently ignores paths that do not exist — it only updates. If the key is absent (perhaps not yet written by an older app version), json_replace() returns the original document unchanged with no warning. Developers expecting upsert behavior end up with missing fields in production.

✓ Instead: Use json_set() when the key may or may not exist; reserve json_replace() only for cases where skipping a missing key is the deliberate intent.

json_replace() is ideal for update operations where you want to guarantee you're updating an existing field — if the field is missing (perhaps the schema changed), the operation silently does nothing rather than inserting an unexpected key. Use json_set() when you want upsert semantics.

◆SQLite Note

No direct 'replace only if exists' equivalent in PostgreSQL JSONB. PostgreSQL's jsonb_set(target, path, value, create_if_missing=false) comes closest. The default jsonb_set() is an upsert. SQLite's json_replace() is strictly 'update existing only'.

jsonb_replace

SQLite 3.45.0 (2024-01-15)→ BLOB

Works identically to json_replace() but returns the modified document as a binary JSONB blob. Only replaces existing keys — does not create new ones.

Signatures

jsonb_replace(json, path, value)

jsonb_replace(json, path1, value1, path2, value2, ...)

Parameters

Parameter	Type	Description
json	TEXT or BLOB	The base JSON document (text or JSONB).
path	TEXT	JSONPath to the element to replace.
value	ANY	Replacement value. Same coercion as json_replace().

Examples

Replace and display as text

sql

SELECT json(jsonb_replace('{"status":"pending"}', '$.status', 'active'));

→'{"status":"active"}'

Update two fields in one call, stays as JSONB

sql

UPDATE tasks SET metadata = jsonb_replace(metadata, '$.status', 'done', '$.completed_at', datetime('now')) WHERE task_id = ?;

→JSONB metadata with status and timestamp updated

Safely update a config value that must already exist

sql

SELECT jsonb_replace(config, '$.max_retries', 5) FROM services WHERE name = 'api';

→JSONB config with max_retries changed

Replace multiple array elements

sql

SELECT json(jsonb_replace('[1,2,3]', '$[0]', 99, '$[2]', 77));

→'[99,2,77]'

Chain jsonb_ operations for a multi-field update

sql

SELECT jsonb_set(jsonb_replace(data, '$.last_seen', datetime('now')), '$.visit_count', json_extract(data,'$.visit_count') + 1) FROM users WHERE id = ?;

→JSONB with updated timestamp and incremented counter

⚠Anti-Pattern— Expecting jsonb_replace to create missing keys

jsonb_replace() is strictly update-only and silently returns the document unchanged when the target path does not exist. A common mistake is using it after a schema change that renamed keys, where the old path no longer exists and updates silently stop being applied with no error.

✓ Instead: Use jsonb_set() for upsert behavior; use jsonb_replace() only when skipping absent keys is the desired outcome.

In a single UPDATE statement, you can combine jsonb_replace() (for fields that must exist) and jsonb_set() (for fields that may or may not exist) by chaining: jsonb_set(jsonb_replace(data, '$.existing_field', val1), '$.maybe_new_field', val2).

◆SQLite Note

Equivalent to PostgreSQL's jsonb_set(target, path, value, false) which does not create missing keys. SQLite separates the create_if_missing flag into distinct functions (json_insert vs json_replace vs json_set) rather than a parameter.

json_set

SQLite 3.9.0 (2015-10-14)→ TEXT

Upserts values in a JSON document. Creates new keys if they do not exist AND overwrites existing keys if they do. Combines the behavior of json_insert() (create) and json_replace() (update). Multiple path/value pairs are applied left to right.

Signatures

json_set(json, path, value)

json_set(json, path1, value1, path2, value2, ...)

Parameters

Parameter	Type	Description
json	TEXT or BLOB	The base JSON document.
path	TEXT	JSONPath to the element to set.
value	ANY	The value to set. TEXT is quoted unless from a JSON function or -> operator.

Examples

Overwrites existing key 'a'

sql

SELECT json_set('{"a":1,"b":2}', '$.a', 99);

→'{"a":99,"b":2}'

Creates new key 'c' since it does not exist

sql

SELECT json_set('{"a":1,"b":2}', '$.c', 99);

→'{"a":1,"b":2,"c":99}'

Upsert pattern: set last_login regardless, create login_count if absent

sql

UPDATE users SET profile = json_set(profile, '$.last_login', datetime('now'), '$.login_count', COALESCE(json_extract(profile,'$.login_count'),0) + 1) WHERE id = ?;

→Profile updated with new last_login and incremented counter

Set multiple nested paths in one call

sql

SELECT json_set(data, '$.config.timeout', 30, '$.config.retries', 3) FROM services;

→JSON with nested config values set

Build a complete object from scratch using an empty doc

sql

SELECT json_set('{}', '$.name', 'Alice', '$.age', 30, '$.tags', json_array('admin','user'));

→'{"name":"Alice","age":30,"tags":["admin","user"]}'

⚠Anti-Pattern— Calling json_set in a loop to update many fields one at a time

Each json_set() call parses the entire JSON document, applies one change, and serializes a new string. Calling it in a loop of N fields costs N full parse-and-serialize cycles. For even 5–10 fields this is measurably slower than a single multi-argument call, and per-row loops in application code can dominate query time.

✓ Instead: Chain all path-value pairs in a single json_set() call: json_set(doc, '$.a', 1, '$.b', 2, '$.c', 3) to parse and serialize only once.

For most application code, json_set() is the safest default: it creates fields that are missing and updates fields that exist. Use json_insert() when you explicitly want to protect existing values from accidental overwrite. Use json_replace() when a missing field should be a bug signal (silent no-op) rather than auto-created.

◆SQLite Note

Equivalent to PostgreSQL's jsonb_set(target, path_array, value, create_missing=true) which is the default upsert behavior. PostgreSQL uses text[] for paths: jsonb_set(data, '{address,city}', '"Portland"'). SQLite uses dot-notation strings: json_set(data, '$.address.city', 'Portland').

jsonb_set

SQLite 3.45.0 (2024-01-15)→ BLOB

Works identically to json_set() (upsert semantics — creates or updates) but returns the result as a binary JSONB blob instead of text.

Signatures

jsonb_set(json, path, value)

jsonb_set(json, path1, value1, path2, value2, ...)

Parameters

Parameter	Type	Description
json	TEXT or BLOB	The base JSON document (text or JSONB).
path	TEXT	JSONPath to the element to set.
value	ANY	The value to set. Same coercion as json_set().

Examples

Set and display as text

sql

SELECT json(jsonb_set('{"a":1}', '$.b', 2));

→'{"a":1,"b":2}'

Extend session expiry in a JSONB column

sql

UPDATE sessions SET state = jsonb_set(state, '$.expires', datetime('now','+1 hour')) WHERE session_id = ?;

→JSONB state with updated expiry

Chain two jsonb_set() calls in one expression

sql

SELECT jsonb_set(jsonb_set(data, '$.status', 'active'), '$.activated_at', datetime('now')) FROM pending_users;

→JSONB with two fields updated

Increment a version field and return the new value

sql

SELECT json_extract(jsonb_set(config, '$.version', json_extract(config,'$.version') + 1), '$.version') FROM app_settings WHERE id = 1;

→New version number after increment

Bootstrap a JSONB document with initial fields

sql

SELECT jsonb_set('{}', '$.created', datetime('now'), '$.modified', datetime('now'), '$.active', 1) FROM (SELECT 1) init;

→JSONB object built from empty document

⚠Anti-Pattern— Using separate jsonb_set() calls per field instead of chaining

Each individual jsonb_set() call parses the BLOB, applies one change, and writes a new BLOB. Separate calls in application code or nested subqueries for each field multiply the parsing cost unnecessarily. The multi-argument form exists precisely to avoid this.

✓ Instead: Pass all path-value pairs to a single jsonb_set() call: jsonb_set(doc, '$.a', v1, '$.b', v2) so the document is parsed and rewritten only once.

When your table has a BLOB column storing JSONB, jsonb_set() gives you the full upsert capability while keeping the data in binary format throughout. For high-frequency updates on frequently-read JSONB columns, this reduces CPU time significantly compared to text JSON functions.

◆SQLite Note

Comparable to PostgreSQL's jsonb_set(target, path, value) which also returns JSONB. PostgreSQL's path is a text array ('{key1,key2}'), SQLite's path is a dot-notation string ('$.key1.key2'). Both create missing keys by default. SQLite's three-way insert/replace/set split does not exist in PostgreSQL.

json_type

SQLite 3.9.0 (2015-10-14)→ TEXT

Returns the type of the outermost JSON element or the element at the specified path. Returns one of: 'null', 'true', 'false', 'integer', 'real', 'text', 'array', or 'object'. Returns SQL NULL if the path does not exist.

Signatures

json_type(json)

json_type(json, path)

Parameters

Parameter	Type	Description
json	TEXT or BLOB	A well-formed JSON string or JSONB blob.
path	TEXT	Optional JSONPath expression. If omitted, returns the type of the root element.

Examples

Root element is an object

sql

SELECT json_type('{"a":1,"b":"hello","c":[1,2]}');

→'object'

Check that '$.tags' is an array before calling json_array_length()

sql

SELECT json_type(data, '$.tags') FROM posts WHERE id = 1;

→'array'

Returns 'true' or 'false' as string type names for JSON booleans

sql

SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}', '$.a[2]');

→'true'

Filter rows by JSON value type

sql

SELECT * FROM documents WHERE json_type(data, '$.value') IN ('integer', 'real');

→Rows where $.value is numeric

Schema-flexible extraction based on runtime type

sql

SELECT
  id,
  CASE json_type(value, '$.config')
    WHEN 'object' THEN json_extract(value, '$.config.host')
    WHEN 'text' THEN value ->> '$.config'
    ELSE NULL
  END AS config_host
FROM services;

→Config host extracted based on config field type

⚠Anti-Pattern— Calling json_array_length or json_each without first checking json_type

json_array_length() and json_each() silently return NULL or an empty result set when the target element is not an array. Omitting a json_type() guard means rows with object-valued or scalar-valued columns pass the query without error but contribute NULL or empty results that can produce incorrect aggregates.

✓ Instead: Guard array operations with WHERE json_type(col, '$.field') = 'array' to exclude non-array values explicitly.

In schema-less JSON columns, the same key might hold different types across rows (string in some, object in others). json_type() lets you branch on the actual type at query time using CASE expressions. This is especially useful during data migrations when you need to handle both old and new schema shapes.

◆SQLite Note

Equivalent to PostgreSQL's jsonb_typeof(jsonb) which returns similar type names ('null', 'boolean', 'number', 'string', 'array', 'object'). Note PostgreSQL uses 'boolean' for both true/false and 'number' for both integer/real, while SQLite distinguishes 'true'/'false' and 'integer'/'real' separately. This distinction is useful for type-precise validation.

json_valid

SQLite 3.9.0 (2015-10-14); flags parameter added in SQLite 3.45.0 (2024-01-15)→ INTEGER (0 or 1)

Returns 1 if the argument is valid JSON (or JSONB), 0 if not. The optional flags bitmask controls what counts as valid: 1=RFC-8259 text (default), 2=JSON5 text, 4=superficially valid JSONB blob, 8=strictly valid JSONB blob. Flags can be OR'd together. Returns NULL if either argument is NULL.

Signatures

json_valid(json)

json_valid(json, flags)

Parameters

Parameter	Type	Description
json	ANY	The value to validate. Does not throw on malformed input.
flags	INTEGER	Optional bitmask: 1=strict JSON, 2=JSON5, 4=probably JSONB, 5=JSON or JSONB, 6=JSON5 or JSONB (recommended for general use), 8=strict JSONB, 9=strict JSON+JSONB. Defaults to 1.

Examples

Valid RFC-8259 JSON

sql

SELECT json_valid('{"x":35}');

→1

JSON5 unquoted key is not valid RFC-8259; returns 0 with default flags=1

sql

SELECT json_valid('{x:35}');

→0

Valid JSON5 — use flags=2 to accept JSON5 extensions

sql

SELECT json_valid('{x:35}', 2);

→1

flags=6 (JSON5+JSONB) is the recommended general-purpose check

sql

SELECT * FROM incoming WHERE json_valid(payload, 6) = 0;

→Rows with invalid JSON or JSONB

Identify rows needing repair before processing

sql

SELECT id, payload FROM import_queue WHERE NOT json_valid(payload);

→Rows with invalid JSON payloads

⚠Anti-Pattern— Using json_valid as a NULL-safe gate without handling NULL input

json_valid(NULL) returns NULL, not 0 or 1. A WHERE clause like WHERE json_valid(col) = 1 silently excludes rows where col IS NULL — the NULL result from json_valid is not equal to 1. This makes json_valid an unreliable gate if the column can be null, causing silent data exclusion.

✓ Instead: Use WHERE json_valid(col) = 1 AND col IS NOT NULL, or COALESCE(json_valid(col), 0) = 1, to treat NULL input as invalid JSON explicitly.

json_valid(x, 6) accepts RFC-8259 JSON, JSON5 extensions, and JSONB blobs — everything that SQLite's JSON functions can process. This is the recommended value when you want to know if a value is a valid input to other JSON functions. Use the default (flags=1) only when you need strict RFC-8259 compliance.

◆SQLite Note

Similar to PostgreSQL's approach of catching exceptions on JSON cast, but SQLite provides a non-throwing function. PostgreSQL equivalent: try CAST(x AS jsonb) and catch the error. Some PostgreSQL setups use a custom is_valid_json() function for this purpose. SQLite json_valid() is cleaner. The flags parameter (added for JSON5/JSONB) has no PostgreSQL equivalent.

json_group_array

SQLite 3.9.0 (2015-10-14)→ TEXT

Aggregate function that collects all values in a group into a JSON array. Respects ORDER BY and DISTINCT within the aggregate (SQLite 3.44.0+ for ORDER BY). Each value is encoded as its JSON equivalent: numbers as numbers, strings as quoted strings, NULL as null, and JSON function outputs as embedded JSON.

Signatures

json_group_array(value)

json_group_array(value ORDER BY ...)

json_group_array(DISTINCT value)

Parameters

Parameter	Type	Description
value	ANY	The value to aggregate. Works like json_array() per element: SQL values become JSON scalars, JSON function results embed as JSON.

Examples

Collect employee names per department into a JSON array

sql

SELECT department, json_group_array(name) AS employees
FROM users
GROUP BY department;

→'{"Engineering":["Alice","Bob"],"Sales":["Carol"]}'

Aggregate full row objects, ordered by score

sql

SELECT json_group_array(json_object('id', id, 'name', name, 'score', score))
FROM players
ORDER BY score DESC;

→'[{"id":3,"name":"Alice","score":1500},{"id":1,"name":"Bob","score":1200}]'

Collect unique tags only

sql

SELECT json_group_array(DISTINCT tag)
FROM post_tags
WHERE post_id = 42;

→'["sql","sqlite","json"]'

Build tree structure for a comment hierarchy

sql

SELECT parent_id, json_group_array(id) AS child_ids FROM comments GROUP BY parent_id;

→Parent → [child IDs] map

Combine aggregate functions in a single JSON response object

sql

SELECT json_object(
  'total', COUNT(*),
  'users', json_group_array(json_object('id', id, 'name', name))
) AS result
FROM users WHERE active = 1;

→'{"total":3,"users":[{"id":1,"name":"Alice"},{"id":2,"name":"Bob"}]}'

⚠Anti-Pattern— Omitting ORDER BY inside json_group_array and relying on row order

Without an explicit ORDER BY inside the aggregate, the order of elements in the resulting JSON array is non-deterministic. SQLite may return rows in any order depending on the query plan, storage layout, or index chosen. Code that relies on the first element, last element, or positional indexing into the array will behave inconsistently.

✓ Instead: Always specify an ORDER BY clause inside the aggregate: json_group_array(name ORDER BY created_at) to guarantee deterministic element order.

Unlike scalar JSON functions, the aggregate functions json_group_array() and json_group_object() process their input as text internally. This means passing JSON function results in their text form (json() or plain json_object()) is more efficient than passing JSONB blobs (jsonb()). The jsonb_ aggregate variants return JSONB output but still process inputs more efficiently as text.

◆SQLite Note

Equivalent to PostgreSQL's json_agg(expression) or jsonb_agg(expression). PostgreSQL's json_agg() preserves SQL NULL as JSON null in the array. SQLite json_group_array() also includes NULLs. PostgreSQL supports json_agg(x ORDER BY y) and json_agg(x) FILTER (WHERE cond) for ordered/filtered aggregation.

jsonb_group_array

SQLite 3.45.0 (2024-01-15)→ BLOB

Aggregate function that works identically to json_group_array() but returns the collected JSON array as a binary JSONB blob instead of text.

Signature

jsonb_group_array(value)

Parameters

Parameter	Type	Description
value	ANY	The value to aggregate into the JSONB array. Note: text inputs are more efficient than JSONB inputs for aggregate functions.

Examples

Aggregate as JSONB then convert to text for display

sql

SELECT json(jsonb_group_array(name)) FROM users GROUP BY department;

→Text JSON arrays per department

Aggregate and store as JSONB for fast future access

sql

INSERT INTO report_cache(data)
SELECT jsonb_group_array(json_object('id', id, 'value', value))
FROM metrics WHERE date = date('now');

→JSONB array stored in report cache

Aggregate IDs and immediately query the length

sql

SELECT json_array_length(jsonb_group_array(id)) AS count_active FROM users WHERE active = 1;

→Count of active user IDs

Embed JSONB aggregate result into another JSONB structure

sql

SELECT jsonb_set(summary, '$.users', jsonb_group_array(user_id)) FROM sessions GROUP BY session_date;

→JSONB summary with user IDs embedded

Aggregate and immediately extract first element

sql

SELECT json_extract(jsonb_group_array(score), '$[0]') AS top_score FROM leaderboard ORDER BY score DESC LIMIT 1;

→Top score from aggregated array

⚠Anti-Pattern— Passing JSONB values as per-row inputs expecting faster aggregation

Unlike scalar jsonb_ functions, jsonb_group_array() processes its per-row inputs internally as text. Passing jsonb(x) as the input value forces an extra BLOB-to-text conversion per row before aggregation. The JSONB benefit of jsonb_group_array() is only in the output blob, not in per-row input handling.

✓ Instead: Pass text JSON (via json_object(), json(), or bare columns) as inputs to jsonb_group_array(); only the output is JSONB.

Unlike scalar jsonb_ functions, aggregate functions do their internal work in text format. So json_group_array(json(x)) is more efficient than json_group_array(jsonb(x)). Use jsonb_group_array() when you need BLOB output, but keep the per-row inputs as text JSON rather than JSONB.

◆SQLite Note

Equivalent to PostgreSQL's jsonb_agg(expression) which aggregates to JSONB. PostgreSQL jsonb_agg() accepts any SQL value and produces a proper JSONB array. SQLite jsonb_group_array() is the JSONB-output variant added in 3.45.0.

json_group_object

SQLite 3.9.0 (2015-10-14)→ TEXT

Aggregate function that constructs a JSON object from all NAME/VALUE pairs in a group. Each row contributes one key-value pair. If the same name appears multiple times, later values overwrite earlier ones (order is not guaranteed unless ORDER BY is used). Returns an empty object '{}' for empty groups.

Signature

json_group_object(name, value)

Parameters

Parameter	Type	Description
name	TEXT	The key name for each row's contribution to the JSON object.
value	ANY	The value. Same coercion rules as json_object().

Examples

Pivot a key-value table into a JSON object

sql

SELECT json_group_object(key, value) AS config_map
FROM app_config
WHERE scope = 'global';

→'{"timeout":30,"max_retries":3,"debug":false}'

Build a lookup map: username → user object

sql

SELECT json_group_object(username, json_object('id', id, 'email', email, 'role', role))
FROM users WHERE active = 1;

→'{"alice":{"id":1,"email":"alice@x.com","role":"admin"},"bob":{...}}'

Build a product_id → quantity map for a shopping cart

sql

SELECT json_group_object(product_id, quantity)
FROM cart_items
WHERE cart_id = 42;

→'{"101":2,"203":1,"305":3}'

Group and pivot salary data per department

sql

SELECT department,
       json_group_object(name, salary) AS salary_map
FROM employees
GROUP BY department;

→Per-department JSON map of name→salary

Build a translations map for an i18n key

sql

SELECT json_group_object(lang, translation)
FROM i18n_strings
WHERE key = 'welcome_message';

→'{"en":"Welcome","fr":"Bienvenue","de":"Willkommen"}'

⚠Anti-Pattern— Using json_group_object with a non-unique key column

When the name column contains duplicate values within a group, json_group_object() silently keeps only the last value for each duplicated key. No error, no warning, and no indication that rows were discarded. In a key-value settings table with stale duplicates, this causes silent data loss depending on row order.

✓ Instead: Ensure the key column is unique within each group, or use a subquery with MAX/MIN to deduplicate before aggregating with json_group_object().

A common pattern in SQLite is storing settings or metadata in a (key, value) table. json_group_object(key, value) turns an entire partition of that table into a single JSON object in one pass — far simpler than multiple self-joins or CASE expressions.

◆SQLite Note

Equivalent to PostgreSQL's json_object_agg(name, value) or jsonb_object_agg(name, value). PostgreSQL's version is stricter: duplicate keys cause an error in some versions, or the last value wins. SQLite json_group_object() silently uses the last value for duplicate keys. PostgreSQL also supports FILTER (WHERE ...) on aggregate functions for conditional aggregation.

jsonb_group_object

SQLite 3.45.0 (2024-01-15)→ BLOB

Aggregate function that works identically to json_group_object() but returns the constructed JSON object as a binary JSONB blob instead of text.

Signature

jsonb_group_object(name, value)

Parameters

Parameter	Type	Description
name	TEXT	The key name for each row's contribution.
value	ANY	The value. Same coercion rules as json_group_object().

Examples

Aggregate to JSONB then convert to text

sql

SELECT json(jsonb_group_object(key, value)) FROM settings WHERE scope = 'app';

→Text JSON object of all app settings

Build and cache a JSONB lookup map

sql

INSERT INTO cached_lookups(map_type, data)
SELECT 'user_by_id', jsonb_group_object(id, json_object('name', name, 'email', email))
FROM users WHERE active = 1;

→JSONB lookup map cached for fast access

Embed aggregated JSONB map into a larger JSONB document

sql

SELECT jsonb_set(report, '$.metadata', jsonb_group_object(field, value))
FROM meta_fields WHERE report_id = 1;

→JSONB report with metadata section populated

Aggregate translations and extract a specific language

sql

SELECT json_extract(jsonb_group_object(lang, value), '$.en')
FROM translations WHERE key = 'app_title';

→English translation string

Confirm the aggregated result is valid JSONB (flag 4)

sql

SELECT json_valid(jsonb_group_object(k, v), 4) FROM kv_store GROUP BY namespace;

→1 for each group (always valid JSONB)

⚠Anti-Pattern— Expecting jsonb_group_object to error on duplicate keys

Like json_group_object(), jsonb_group_object() silently overwrites earlier values when the same key appears multiple times in the group. No error is raised, so duplicate-key bugs in a source table produce silently truncated JSONB objects that appear valid but are missing rows.

✓ Instead: Deduplicate the key column before the aggregate, or add a check upstream to detect and reject duplicate keys before they reach the aggregate.

When building lookup maps from relational tables (e.g., id→name mappings), jsonb_group_object() lets you store the map as JSONB in one INSERT...SELECT. Subsequent lookups using json_extract() on the JSONB blob are significantly faster than repeated JOIN queries.

◆SQLite Note

Equivalent to PostgreSQL's jsonb_object_agg(name, value). Both return a key-value JSONB map. PostgreSQL's version errors on NULL keys; SQLite's json_group_object silently skips rows where name is NULL.

json_each

SQLite 3.9.0 (2015-10-14)→ TABLE (key, value, type, atom, id, parent, fullkey, path)

Table-valued function that generates one row per immediate child of the top-level JSON array or object. With a path argument, starts at the specified nested element instead of the root. Columns: key, value, type, atom, id, parent (always NULL for json_each), fullkey, path. The 'value' column returns text JSON for arrays/objects and native SQL types for primitives.

Signatures

json_each(json)

json_each(json, path)

Parameters

Parameter	Type	Description
json	TEXT or BLOB	A well-formed JSON string or JSONB blob to iterate over.
path	TEXT	Optional JSONPath to start iteration from a nested element instead of the root.

Examples

Iterate over object keys — key is field name, value is native SQL type for scalars

sql

SELECT key, value, type
FROM json_each('{"name":"Alice","age":30,"active":true}');

→

key='name' value='Alice' type='text'
key='age' value=30 type='integer'
key='active' value=1 type='true'

Iterate over an array — key is the integer index (0,1,2), value is the element

sql

SELECT value FROM json_each('["apple","banana","cherry"]');

→

'apple'
'banana'
'cherry'

JOIN with json_each to search within JSON arrays stored in a column

sql

SELECT DISTINCT u.name
FROM users u, json_each(u.phone_numbers) j
WHERE j.value LIKE '503-%';

→User names with a 503 area code phone

Iterate only the nested '$.address' object using the path argument

sql

SELECT j.key, j.value
FROM documents d, json_each(d.data, '$.address')
WHERE d.id = 1;

→street='123 Main', city='Portland', zip='97201'

Use json_each to dynamically inspect JSON structure

sql

SELECT j.key AS field, j.type, j.value
FROM config c, json_each(c.settings)
WHERE c.scope = 'app'
ORDER BY j.key;

→All config keys with their types and values

⚠Anti-Pattern— Using json_each without joining it back to the parent table

json_each() is a table-valued function and must be properly correlated to its source table. Writing FROM json_each('[1,2,3]') alongside another table without a JOIN condition produces a cartesian product — every row from the table is paired with every element of the array silently, with no error.

✓ Instead: Always join json_each to its parent table by referencing the column: FROM posts p, json_each(p.tags) t WHERE t.value = 'sql'.

When joining json_each() with a table, reference the virtual table columns using the function name as alias: FROM users u, json_each(u.tags) t WHERE t.value = 'admin'. Without the alias, SQLite may be ambiguous about whether 'value' refers to the json_each virtual column or a user table column. The columns key, value, type, atom, fullkey, and path are all available.

◆SQLite Note

Equivalent to PostgreSQL's json_each(json) or jsonb_each(jsonb) table functions. PostgreSQL returns (key text, value json) pairs. SQLite returns more columns including type, atom, id, parent, fullkey, path. PostgreSQL also has json_each_text() which returns text values. SQLite's ->> operator achieves similar scalar extraction. jsonb_each() variant (SQLite 3.51.0+) returns JSONB for object/array values instead of text.

jsonb_each

SQLite 3.51.0 (2025-11-04)→ TABLE (key, value, type, atom, id, parent, fullkey, path)

Works identically to json_each() with the same column schema, except the 'value' column returns JSONB blobs instead of text JSON when the type is 'array' or 'object'. For primitive types (integer, real, text, null, true, false), it returns the same native SQL values as json_each().

Signatures

jsonb_each(json)

jsonb_each(json, path)

Parameters

Parameter	Type	Description
json	TEXT or BLOB	A well-formed JSON string or JSONB blob to iterate over.
path	TEXT	Optional JSONPath to start iteration from a nested element.

Examples

Wrap value with json() when type is array/object to get readable text

sql

SELECT key, json(value) AS text_value, type
FROM jsonb_each('{"scores":[1,2,3],"name":"Alice"}');

→

key='scores' text_value='[1,2,3]' type='array'
key='name' text_value='Alice' type='text'

Since value is JSONB for arrays, pass directly to json_array_length()

sql

SELECT key, json_array_length(value) AS len
FROM jsonb_each(data)
WHERE type = 'array';

→Array fields with their lengths

Modify each sub-object and keep result as JSONB

sql

SELECT key, jsonb_set(value, '$.processed', 1) AS updated_val
FROM jsonb_each(batch_data)
WHERE type = 'object';

→Updated JSONB objects from each row of the batch

Extract a field from each nested object using jsonb value directly

sql

SELECT j.key, json_extract(j.value, '$.status') AS status
FROM jsonb_each(workflow_steps) j
WHERE j.type = 'object';

→Step name and its status field

Count nested object keys

sql

SELECT parent_key, count(*) AS child_count
FROM (
  SELECT key AS parent_key
  FROM jsonb_each(nested_data)
  WHERE type = 'object'
) t;

→Count of top-level object children

⚠Anti-Pattern— Using jsonb_each without joining it back to the parent table

jsonb_each() shares the same table-valued function join requirement as json_each(). Using it in a FROM clause without referencing the parent table column creates a cartesian product — every row of the parent table is multiplied by every element of the parsed array, with no query error to indicate the mistake.

✓ Instead: Always reference the parent table column inside jsonb_each(): FROM orders o, jsonb_each(o.items) j so that each call is scoped to the current row.

If your json_each() loop processes nested objects or arrays (type='object' or type='array') and you want to modify them with jsonb_set(), jsonb_insert(), etc., use jsonb_each() instead. The value column already comes back as JSONB, skipping a text parse step for each sub-document.

◆SQLite Note

PostgreSQL's jsonb_each(jsonb) similarly returns (key text, value jsonb) pairs where value is JSONB. SQLite jsonb_each() adds extra columns (type, atom, id, parent, fullkey, path) not present in PostgreSQL's version. Available since SQLite 3.51.0 — use json_each() for compatibility with older SQLite versions.

json_tree

SQLite 3.9.0 (2015-10-14)→ TABLE (key, value, type, atom, id, parent, fullkey, path)

Table-valued function that recursively walks the entire JSON structure, returning one row for every element at every level of nesting. Unlike json_each() which only visits immediate children, json_tree() visits all nodes in the tree. The same columns are returned: key, value, type, atom, id, parent, fullkey, path. The 'parent' column is populated (unlike in json_each where it is always NULL).

Signatures

json_tree(json)

json_tree(json, path)

Parameters

Parameter	Type	Description
json	TEXT or BLOB	A well-formed JSON string or JSONB blob to walk recursively.
path	TEXT	Optional JSONPath specifying a subtree root to start from. Only that subtree is walked.

Examples

Walk all leaves — filter WHERE atom IS NOT NULL to skip containers

sql

SELECT fullkey, type, atom
FROM json_tree('{"a":{"b":1,"c":[2,3]},"d":4}')
WHERE atom IS NOT NULL;

→

fullkey='$.a.b' type='integer' atom=1
fullkey='$.a.c[0]' type='integer' atom=2
fullkey='$.a.c[1]' type='integer' atom=3
fullkey='$.d' type='integer' atom=4

Deep search for a specific value buried anywhere in a nested structure

sql

SELECT DISTINCT json_extract(big.json, '$.id') AS doc_id
FROM big, json_tree(big.json, '$.partlist')
WHERE json_tree.key = 'uuid'
  AND json_tree.value = '6fa5181e-5721-11e5-a04e-57f3d7b32808';

→doc_id values containing the target UUID anywhere in $.partlist

Full-tree text search across all string values

sql

SELECT fullkey, value
FROM json_tree('[{"id":1,"tags":["sql"]},{"id":2,"tags":["nosql","redis"]}]')
WHERE type = 'text' AND value LIKE '%sql%';

→Paths where value matches pattern

Decompose all JSON documents in a table into (rowid, path, value) triples

sql

SELECT big.rowid, fullkey, atom
FROM big, json_tree(big.json)
WHERE json_tree.type NOT IN ('object', 'array');

→All scalar leaf values with their full paths

Count total nodes and leaf nodes in a JSON document

sql

SELECT count(*) AS total_nodes,
       count(CASE WHEN type NOT IN ('object','array') THEN 1 END) AS leaf_count
FROM json_tree(data)
FROM documents WHERE id = 1;

→total_nodes=12, leaf_count=8

⚠Anti-Pattern— Using json_tree when only top-level iteration is needed

json_tree() walks every node at every nesting depth recursively. For a deeply nested document it can generate dozens or hundreds of rows per document even when the query only needs immediate children. Using json_tree() where json_each() suffices significantly increases the number of rows processed and slows the query.

✓ Instead: Use json_each() for top-level iteration; only use json_tree() when you genuinely need to search or process all nested levels.

json_tree() returns every node including containers (objects and arrays). For most search and decomposition tasks, you only want leaf (primitive) values. The cleanest way to filter: WHERE atom IS NOT NULL — because 'atom' is populated for primitives and NULL for containers. Alternatively: WHERE type NOT IN ('object', 'array').

◆SQLite Note

No direct equivalent in PostgreSQL. PostgreSQL's jsonb_each() and json_each() are non-recursive (like SQLite's json_each). PostgreSQL has jsonb_path_query() for JSONPath queries which can traverse nested structures. For full recursive tree walking in PostgreSQL, you'd use jsonb_populate_recordset() or recursive CTEs. SQLite json_tree() is uniquely powerful for deep JSON searches.

jsonb_tree

SQLite 3.51.0 (2025-11-04)→ TABLE (key, value, type, atom, id, parent, fullkey, path)

Works identically to json_tree() — recursively walks the entire JSON structure depth-first — but the 'value' column returns JSONB blobs instead of text JSON when the type is 'array' or 'object'. For primitive types, the behavior is identical to json_tree().

Signatures

jsonb_tree(json)

jsonb_tree(json, path)

Parameters

Parameter	Type	Description
json	TEXT or BLOB	A well-formed JSON string or JSONB blob to walk recursively.
path	TEXT	Optional JSONPath to start recursive walk from a specific subtree.

Examples

Walk all leaves; value is JSONB for containers but we filter them out here

sql

SELECT fullkey, json(value) AS text_val, type
FROM jsonb_tree('{"a":{"b":1},"c":[2,3]}')
WHERE type NOT IN ('object','array');

→

fullkey='$.a.b' text_val='1' type='integer'
fullkey='$.c[0]' text_val='2' type='integer'
fullkey='$.c[1]' text_val='3' type='integer'

Find and modify sub-objects deep in the tree, value already JSONB

sql

SELECT fullkey, jsonb_set(value, '$.processed', 1)
FROM jsonb_tree(document)
WHERE type = 'object' AND json_extract(value, '$.status') = 'pending';

→Updated JSONB sub-objects where status was pending

Find all arrays and their lengths using JSONB value directly

sql

SELECT fullkey, json_array_length(value) AS array_len
FROM jsonb_tree(data)
WHERE type = 'array';

→Every array in the document with its length

Deep search using jsonb value of sub-objects directly

sql

SELECT DISTINCT json_extract(big.json, '$.id')
FROM big, jsonb_tree(big.json, '$.dependencies')
WHERE jsonb_tree.type = 'object'
  AND json_extract(jsonb_tree.value, '$.version') LIKE '2.%';

→IDs of documents with v2.x dependencies anywhere in tree

Count nested arrays across the whole document tree

sql

SELECT count(*) FROM jsonb_tree(payload) WHERE type = 'array';

→Count of all arrays at any nesting level

⚠Anti-Pattern— Using jsonb_tree for top-level iteration instead of jsonb_each

jsonb_tree() recursively visits every node in the JSON tree at all depths. For large or deeply nested documents it produces far more rows than jsonb_each(), which only visits immediate children. The extra rows increase memory usage and slow the query without providing useful data if only the top level is needed.

✓ Instead: Use jsonb_each() when only the immediate children of the root (or a specified path) need to be iterated; use jsonb_tree() only for genuinely deep recursive searches.

If you use json_tree() to locate sub-documents (type='object' or type='array') and then pass the 'value' column to jsonb_set() or other jsonb_ functions, use jsonb_tree() instead — the value column comes back as JSONB, eliminating a text parse step per sub-document.

◆SQLite Note

No PostgreSQL equivalent for recursive JSON tree walking by this name. PostgreSQL's jsonb_path_query(jsonb, jsonpath) can traverse nested paths with JSONPath expressions. SQLite jsonb_tree() is the JSONB-value variant of json_tree(). Both json_tree() and jsonb_tree() are unique to SQLite's JSON function set. Added in 3.51.0.

Related SQLite Categories

Core Functions →Aggregate Functions →String Functions →

SQLite JSON Functions

Jump to function

What are SQLite JSON Functions?

json

Signature

Parameters

Examples

jsonb

Signature

Parameters

Examples

json_array

Signatures

Parameters

Examples

jsonb_array

Signatures

Parameters

Examples

json_array_length

Signatures

Parameters

Examples

json_error_position

Signature

Parameters

Examples

json_extract

Signatures

Parameters

Examples

jsonb_extract

Signatures

Parameters

Examples

json_insert

Signatures

Parameters

Examples

jsonb_insert

Signatures

Parameters

Examples

json_object

Signatures

Parameters

Examples

jsonb_object

Signatures

Parameters

Examples

json_patch

Signature

Parameters

Examples

jsonb_patch

Signature

Parameters

Examples

json_pretty

Signatures

Parameters

Examples

json_quote

Signature

Parameters

Examples

json_remove

Signatures

Parameters

Examples

jsonb_remove

Signatures

Parameters

Examples

json_replace

Signatures

Parameters

Examples

jsonb_replace