SQLite 3 reference · 19 entries
SQLite JSON Functions
SQLite JSON functions and operators: json_extract(), ->, ->>, json_object(), json_set(), json_each(), json_group_array() and more, with runnable examples.
JSON is stored as ordinary text (or as binary JSONB since 3.45). Paths start with $: '$.user.name',
'$.items[0]', and '$.items[#-1]' for the last element.
| Name | Description | Since |
|---|---|---|
JSON() |
Check and minify JSON text: return it without extra whitespace, or raise an error if it isn't valid JSON. | 3.9.0 |
JSON_EXTRACT() |
Read one or more values from JSON by path. JSON objects and arrays come back as text; numbers and strings as SQL values. | 3.9.0 |
-> and ->> |
Extract from JSON with -> (returns JSON text) or ->> (returns an SQL value). Paths can be '$.key' or just a key or index. | 3.38.0 |
JSON_OBJECT() |
Build a JSON object from key and value pairs. SQL NULL becomes JSON null; nested json() values stay JSON. | 3.9.0 |
JSON_ARRAY() |
Build a JSON array from the arguments. SQL NULL becomes JSON null; numbers stay numbers. | 3.9.0 |
JSON_ARRAY_LENGTH() |
Return the number of elements in a JSON array, optionally at a path. Non-arrays give 0. | 3.9.0 |
JSON_TYPE() |
Return the JSON type of a value or path: 'null', 'true', 'false', 'integer', 'real', 'text', 'array' or 'object'. | 3.9.0 |
JSON_VALID() |
Return 1 if a string is well-formed JSON and 0 if it isn't. Use it in CHECK constraints or before parsing. | 3.9.0 |
JSON_SET() |
Return JSON with values set at paths, creating or overwriting them. json_insert() and json_replace() are stricter. | 3.9.0 |
JSON_INSERT() |
Return JSON with values added at paths that don't exist yet. Existing values are left unchanged. | 3.9.0 |
JSON_REPLACE() |
Return JSON with values replaced at paths that already exist. Missing paths are not created. | 3.9.0 |
JSON_REMOVE() |
Return JSON with the elements at the given paths removed. Paths that don't exist are ignored. | 3.9.0 |
JSON_PATCH() |
Merge one JSON object into another using RFC 7396 MergePatch: keys are added or replaced, null removes a key. | 3.18.0 |
JSON_GROUP_ARRAY() |
Aggregate the values of a group into one JSON array, one element per row. SQL NULLs become JSON null. | 3.10.0 |
JSON_GROUP_OBJECT() |
Aggregate key/value pairs from the rows of a group into one JSON object, such as a name-to-price lookup. | 3.10.0 |
JSON_EACH() |
A table-valued function that returns one row per element of a JSON array or object, with key, value and type columns. | 3.9.0 |
JSON_TREE() |
A table-valued function like json_each() that walks a JSON value recursively, returning a row for every nested element. | 3.9.0 |
JSONB() |
Convert JSON text to SQLite's binary JSONB format, which is faster to process. Every json_ function has a jsonb_ version. | 3.45.0 |
JSON_PRETTY() |
Return JSON formatted with new lines and indentation (4 spaces by default) so it's easy to read. | 3.46.0 |