SQL Reference ยท JSON and Special Features
SQL json_extract() function and -> operators
Reference for reading JSON in SQL: json_extract(), the -> and ->> operators, JSON paths, NULL results and the equivalents in PostgreSQL, MySQL and SQL Server.
json_extract(json, path) reads one value out of a JSON document. The path says where to look: $ is the whole document, $.name a key, $.tags[0] the first array element. Text and numbers come back as ordinary SQL values; objects and arrays come back as JSON text.
The operators -> and ->> are shorthand. -> returns the result as JSON (strings keep their quotes), ->> returns an SQL value, like json_extract() does for scalars. The same ideas exist in MySQL and PostgreSQL; SQL Server and Oracle use JSON_VALUE/JSON_QUERY and the SQL standard defines JSON_VALUE and JSON_QUERY too.
SELECT json_extract('{"user": {"name": "Amina"}, "tags": ["a", "b"]}', '$.user.name') AS name, '{"tags": ["a", "b"]}' ->> '$.tags[1]' AS second_tag;
Try it
Press Run (or Ctrl + Enter) to see the result here.
type_json keeps the quotes ("signup") because -> returns JSON text. Mei's first_tag is NULL (there is no element 0), and Amina's amount is NULL because the JSON value is null. Neither case is an error.
Press Run (or Ctrl + Enter) to see the result here.
With two paths, json_extract() returns a JSON array of the results. The signup event has no amount, so ->> gives NULL; SUM() ignores NULLs and returns NULL for a group where every value is NULL (the signup row).
Syntax
json_extract(json, path [, path ...]) json -> path json ->> path
| Part | Description |
|---|---|
json |
A JSON text value (or JSONB blob in SQLite 3.45+). Invalid JSON raises an error such as malformed JSON. |
path |
A JSON path starting with $. For -> and ->>, a plain key name ('type') or an array index (0) is also accepted. |
Path forms (SQLite):
| Path | Selects |
|---|---|
$ |
the whole document |
$.key |
a key of the top-level object |
$.a.b |
a nested key |
$."key with spaces" |
a key that needs quotes |
$[0] / $.list[2] |
an array element (counted from 0) |
$.list[#-1] |
the last element of the array |
Returns: for scalars, the SQL value (TEXT, INTEGER, REAL, or NULL for JSON null and missing paths). For objects and arrays, JSON text. JSON true and false become 1 and 0.
Dialect differences
| Database | Scalar as text | Sub-document | Notes |
|---|---|---|---|
| SQLite | ->>, json_extract |
-> |
Keys may be written without $. in the arrows. |
| PostgreSQL | ->> (key or index), #>> (path array) |
->, #> |
Paths are not $ strings; jsonb_path_query accepts them. Arrays count from 0. |
| MySQL | ->>, JSON_UNQUOTE(JSON_EXTRACT()) |
->, JSON_EXTRACT |
Same $ paths as SQLite. JSON_EXTRACT returns JSON, so strings keep their quotes. |
| SQL Server | JSON_VALUE(doc, '$.a') |
JSON_QUERY(doc, '$.a') |
JSON_VALUE returns NULL on non-scalars in the default lax mode. |
| Oracle | JSON_VALUE(doc, '$.a') |
JSON_QUERY(doc, '$.a') |
Also dot notation on IS JSON columns. |
-- PostgreSQL SELECT payload ->> 'type', payload -> 'user' ->> 'name', payload #>> '{tags,0}' FROM events; -- MySQL SELECT payload ->> '$.type', JSON_EXTRACT(payload, '$.tags[0]') FROM events; -- SQL Server / Oracle SELECT JSON_VALUE(payload, '$.user.name') FROM events;
Usage notes
- Use
->>for comparing and calculating.payload -> 'type' = 'signup'is false because the left side is the text"signup"with quotes. - A missing path is
NULL, not an error. A typo in a key name silently returnsNULLin every row. - Numbers vs text.
->>on a JSON string"42"returns text; cast it before arithmetic. - Performance. Each call parses the document. Index the expression you filter on,
CREATE INDEX i ON events ((payload ->> 'type'))(SQLite also accepts it without the extra parentheses), or move hot fields into real columns. - Validate on insert with
CHECK (json_valid(payload)). - Types in PostgreSQL. Use
jsonbrather thanjsonfor faster extraction and indexing.