SQLite 3 reference · JSON Functions
SQLite JSON_EXTRACT() Function
Read one or more values from JSON by path. JSON objects and arrays come back as text; numbers and strings as SQL values.
SQLite 3 Since 3.9.0
Syntax
json_extract(json, path, ...)
Parameters
| Parameter | Description |
|---|---|
json |
JSON text (or JSONB). |
path |
A path such as '$.name', '$.items[0]' or '$.items[#-1]' (last element). |
Return value
An SQL value, JSON text, or NULL when the path doesn't exist.
Examples
SELECT json_extract('{"user":{"name":"Ada","langs":["en","fr"]}}', '$.user.name') AS name,
json_extract('{"user":{"name":"Ada","langs":["en","fr"]}}', '$.user.langs[1]') AS second_lang;
Output
name | second_lang Ada | fr
SELECT json_extract('{"a":1,"b":[2,3]}', '$.a', '$.b') AS several;
Output
several [1,[2,3]]
Version history
| Version | Change |
|---|---|
| SQLite 3.9.0 | Added |
| SQLite 3.38.0 | Built in by default; earlier versions needed the JSON1 extension. |
In other languages
-> and ->>· PostgreSQL
Related
-> and ->>· Extract from JSON with -> (returns JSON text) or ->> (returns an SQL value). Paths can be '$.key' or just a key or index.JSON_EACH()· A table-valued function that returns one row per element of a JSON array or object, with key, value and type columns.JSON_TYPE()· Return the JSON type of a value or path: 'null', 'true', 'false', 'integer', 'real', 'text', 'array' or 'object'.- SQL String Functions · tutorial