Skip to content
Tutorials Center
My learning

Topics

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.

SQL
SELECT
  json_extract('{"user": {"name": "Amina"}, "tags": ["a", "b"]}', '$.user.name') AS name,
  '{"tags": ["a", "b"]}' ->> '$.tags[1]' AS second_tag;

Try it

SQL json_extract, -> and ->>

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.

SQL Filtering and calculating on JSON fields

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

SQL
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.
SQL
-- 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 returns NULL in 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 jsonb rather than json for faster extraction and indexing.