JSON_EXTRACT()

Syntax

How it works

A JSON column holds a whole document, but most queries only need one field from it. JSON_EXTRACT() and its cousins take a JSON path expression and return the value at that location. Paths start at the document root $, then walk into object keys with $.key and into array elements with $.arr[0]. You can nest them, for example $.address.city.

The crux is that the syntax is not portable. In MySQL you write JSON_EXTRACT(doc, '$.path'), or the shorthand doc -> '$.path' which returns the value still wrapped in JSON quotes, or doc ->> '$.path' which returns unquoted text. SQLite uses the same json_extract(doc, '$.path'). In PostgreSQL the operators take a bare key: doc -> 'key' returns JSON and doc ->> 'key' returns text, with #> and jsonb_path_query for deeper paths. In SQL Server you use JSON_VALUE(doc, '$.path') for a scalar and JSON_QUERY(doc, '$.path') for an object or array.

Because the return type matters, watch the quoting: JSON_EXTRACT() and -> preserve JSON (a string comes back as "Cairo" with the quotes), while ->>, JSON_UNQUOTE() and JSON_VALUE() hand you the plain text Cairo. If you need to compare or CAST the value, you almost always want the unquoted form.

Examples

Extract a top-level key (MySQL / SQLite)

-- profile holds {"name": "Sara", "age": 30}
SELECT JSON_EXTRACT(profile, '$.name') AS name_json,
       JSON_UNQUOTE(JSON_EXTRACT(profile, '$.name')) AS name_text
FROM users
WHERE id = 1;

name_json | name_text
----------+----------
 "Sara"   | Sara

Extract a nested value

-- doc holds {"address": {"city": "Cairo", "zip": "11511"}}
SELECT JSON_EXTRACT(doc, '$.address.city') AS city
FROM orders
WHERE id = 42;

city
-------
 Cairo

Extract an array element

-- tags holds ["sql", "json", "index"]
SELECT JSON_EXTRACT(tags, '$[0]') AS first_tag,
       JSON_EXTRACT(tags, '$[2]') AS third_tag
FROM posts
WHERE id = 7;

first_tag | third_tag
----------+----------
 "sql"    | "index"

The same extract in every engine

-- MySQL (unquoted text)
SELECT profile ->> '$.name' FROM users;

-- PostgreSQL (operator takes a bare key, ->> returns text)
SELECT profile ->> 'name' FROM users;

-- SQL Server (JSON_VALUE for a scalar)
SELECT JSON_VALUE(profile, '$.name') FROM users;

-- SQLite
SELECT json_extract(profile, '$.name') FROM users;

name
------
 Sara

Filter rows on a JSON field in WHERE

-- MySQL / SQLite: keep only VIP customers
SELECT id, JSON_UNQUOTE(JSON_EXTRACT(profile, '$.tier')) AS tier
FROM customers
WHERE JSON_UNQUOTE(JSON_EXTRACT(profile, '$.tier')) = 'vip';

-- PostgreSQL equivalent
SELECT id, profile ->> 'tier' AS tier
FROM customers
WHERE profile ->> 'tier' = 'vip';

id  | tier
----+-----
  3 | vip
 17 | vip

Extract an object with JSON_QUERY (SQL Server)

-- JSON_VALUE returns NULL for an object; use JSON_QUERY
SELECT JSON_QUERY(doc, '$.address')  AS address_obj,
       JSON_VALUE(doc, '$.address.city') AS city
FROM orders
WHERE id = 42;

address_obj                        | city
-----------------------------------+-------
 {"city":"Cairo","zip":"11511"}    | Cairo

Common mistakes

-- Comparing a quoted JSON value to plain text always fails:
-- JSON_EXTRACT returns "vip" (with quotes), not vip
SELECT * FROM customers
WHERE JSON_EXTRACT(profile, '$.tier') = 'vip';

-- Unquote first with ->> or JSON_UNQUOTE
SELECT * FROM customers
WHERE profile ->> '$.tier' = 'vip';
-- or: WHERE JSON_UNQUOTE(JSON_EXTRACT(profile, '$.tier')) = 'vip'

-- Reads and parses the JSON of every single row.
-- On a large table this is a full scan.
SELECT * FROM customers
WHERE JSON_UNQUOTE(JSON_EXTRACT(profile, '$.tier')) = 'vip';

-- Add a generated / functional index on the extracted value
-- MySQL:
ALTER TABLE customers
  ADD tier VARCHAR(20)
  AS (JSON_UNQUOTE(JSON_EXTRACT(profile, '$.tier'))) STORED,
  ADD INDEX idx_tier (tier);
-- PostgreSQL:
CREATE INDEX idx_tier ON customers ((profile ->> 'tier'));

-- Typo in the path: 'citty' does not exist.
-- This returns NULL, not an error, so the bug is silent.
SELECT JSON_EXTRACT(doc, '$.address.citty') AS city
FROM orders;

-- Use the correct key. NULL means the path missed.
SELECT JSON_EXTRACT(doc, '$.address.city') AS city
FROM orders;

Performance

Extracting from JSON means parsing the document at query time. For a single row by primary key that is cheap, but filtering or grouping on a JSON path across a big table forces the engine to parse every row, which is a full scan. Whenever a JSON field appears in WHERE, JOIN or ORDER BY on a large table, plan for an index.

The standard fix is a generated (computed) column that materialises the extracted value, plus a normal index on it. MySQL supports STORED and VIRTUAL generated columns, PostgreSQL supports functional (expression) indexes such as CREATE INDEX ... ((profile ->> 'tier')), and SQL Server lets you index a computed column built from JSON_VALUE(). PostgreSQL jsonb also offers GIN indexes for containment queries.

Prefer jsonb over json in PostgreSQL: jsonb is parsed and stored in a binary form, so extraction and indexing are much faster. See the indexing guide for how expression indexes are used by the optimiser.

Interview questions

SELECT profile -> '$.name'  AS as_json,   -- "Sara"
       profile ->> '$.name' AS as_text;  -- Sara