SQLite JSON Functions

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]"}.

INSERT INTO events(data) VALUES(json('{"type":"click","target":"btn"}'));

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.

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.

INSERT INTO cache(payload) VALUES(jsonb('{"a":1,"b":2}'));

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.

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).

SELECT json_array(id, name, score) AS row_json FROM leaderboard LIMIT 3;

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.

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(...), ...), ...)).

SELECT jsonb_array('x', 42, null) AS result;

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() 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.

SELECT id, json_array_length(tags) AS tag_count FROM posts WHERE json_array_length(tags) > 5;

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.

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.

SELECT json_error_position(user_input) AS err_pos FROM form_data WHERE json_error_position(user_input) != 0;

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.

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.

SELECT json_extract(profile, '$.address.city') AS city FROM users WHERE json_extract(profile, '$.verified') = 1;

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.

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.

SELECT jsonb_extract(payload, '$.event') AS event_type FROM logs WHERE jsonb_extract(payload, '$.level') = 'error';

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().

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.

UPDATE settings SET config = json_insert(config, '$.notifications.email', 1) WHERE user_id = 42;

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.

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.

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

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.

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.

SELECT json_object('id', id, 'name', name, 'score', score) AS row_obj FROM players LIMIT 1;

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.

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.

SELECT jsonb_object('status', 'ok', 'count', 42) AS response;

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.

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.

UPDATE docs SET data = json_patch(data, '{"status":"published","draft":null}') WHERE id = 7;

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.

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.

SELECT jsonb_patch('{"a":1,"b":2}', '{"b":null,"c":3}');

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() 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.

SELECT json_pretty(config) FROM app_settings WHERE id = 1;

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.

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.

SELECT '[' || group_concat(json_quote(tag)) || ']' AS tags_json FROM post_tags WHERE post_id = 5;

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().

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.

UPDATE users SET preferences = json_remove(preferences, '$.experimental_features', '$.beta_opt_in') WHERE id = 1;

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.

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')).

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

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() 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.

UPDATE sessions SET data = json_replace(data, '$.last_seen', datetime('now')) WHERE token = 'abc123';

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'.

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).

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

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.

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.

UPDATE profiles SET data = json_set(data, '$.settings.theme', 'dark', '$.settings.lang', 'en') WHERE user_id = 99;

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').

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.

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

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.

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.

SELECT id, json_type(payload) AS root_type, json_type(payload, '$.items') AS items_type FROM requests WHERE id = 1;

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(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.

SELECT count(*) AS invalid_rows FROM imports WHERE json_valid(raw_data) = 0;

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.

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.

SELECT user_id, json_group_array(json_object('id', order_id, 'total', total)) AS orders FROM orders GROUP BY user_id;

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.

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.

SELECT category, jsonb_group_array(json_object('id', id, 'name', name)) AS items FROM products GROUP BY category;

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.

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.

SELECT json_group_object(key, value) AS config_json FROM app_config WHERE app_id = 1;

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.

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.

SELECT jsonb_group_object(setting_key, setting_value) AS settings FROM user_preferences WHERE user_id = 7;

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.

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.

SELECT j.key, j.value FROM orders, json_each(orders.tags) j WHERE orders.id = 1;

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.

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.

SELECT j.key, j.value FROM config, jsonb_each(config.data) j WHERE config.env = 'prod';

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() 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').

SELECT path, type, value FROM logs, json_tree(logs.payload) WHERE type = 'text' AND value LIKE '%error%';

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.

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.

SELECT path, type FROM documents, jsonb_tree(documents.content) WHERE type = 'array';

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.