JSON EXTRACTJSON
Buy Me A Coffee

Extract a scalar value or sub-document from a JSON column using a path expression.

Extracts values from JSON data: JSON_EXTRACT returns the raw JSON (including delimiters) while JSON_VALUE returns a scalar string. JSON_VALUE in SQL Server is the equivalent of JSON_EXTRACT in PostgreSQL. Essential for querying semi-structured data.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 5.7.8 JSON_EXTRACT(col, path) returns a JSON-encoded value. The -> operator is shorthand for JSON_EXTRACT; ->> is shorthand for JSON_UNQUOTE(JSON_EXTRACT(...)) and returns unquoted text. Paths use $.key, $.key.subkey, $.array[n] notation.
PostgreSQL ✓ Supported 9.3 The -> operator returns a JSON value; ->> returns text. For nested paths use #> (JSON) and #>> (text) with an array of keys. jsonb columns support the same operators plus GIN-indexable containment operators (@>, <@, ?, ?|, ?&). json_extract_path() and jsonb_extract_path_text() are function equivalents.
SQL Server ✓ Supported 2016 JSON_VALUE(col, path) extracts a scalar (returns NVARCHAR). JSON_QUERY(col, path) extracts an object or array (returns NVARCHAR containing JSON). Passing the wrong function for the value type returns NULL silently. No -> operators — all access is function-based with $.key path strings.
Oracle ✓ Supported 12c JSON_VALUE(col, path) extracts a scalar; JSON_QUERY(col, path) extracts objects or arrays. The RETURNING clause controls the output type. Error handling clauses (NULL ON ERROR, ERROR ON ERROR) prevent silent failures. Oracle 21c added JSON_TRANSFORM and dot-notation access on JSON type columns.
SQLite ✓ Supported 3.9.0 json_extract(col, path) is the core function, available since 3.9.0. The -> and ->> operators (matching MySQL semantics) were added in SQLite 3.38.0 (2022-02-22). ->> always returns an SQL value (text/integer/real), never a JSON-encoded string.

Details

Path syntax is consistent across engines ($.key, $.nested.key, $.array[n]) but function names and operators diverge sharply. PostgreSQL alone uses #> / #>> for multi-key nested paths. SQL Server splits extraction into JSON_VALUE (scalars only) vs JSON_QUERY (objects/arrays only) — passing the wrong function silently returns NULL instead of an error. SQLite's -> / ->> operators arrived in 3.38.0; apps targeting older SQLite must use json_extract().

Standard Syntax

-- MySQL / SQLite path syntax: SELECT JSON_EXTRACT(data, '$.name') AS name FROM users; -- PostgreSQL operator shorthand: SELECT data->>'name' AS name FROM users; -- SQL Server / Oracle: SELECT JSON_VALUE(data, '$.name') AS name FROM users;

Version Support

MySQL: Since 5.7.8 PostgreSQL: Since 9.3 SQL Server: Since 2016 Oracle: Since 12c SQLite: Since 3.9.0

Per-Database Syntax & Notes

MySQL

JSON_EXTRACT(col, path) returns a JSON-encoded value. The -> operator is shorthand for JSON_EXTRACT; ->> is shorthand for JSON_UNQUOTE(JSON_EXTRACT(...)) and returns unquoted text. Paths use $.key, $.key.subkey, $.array[n] notation.

-- Function form: SELECT JSON_EXTRACT(data, '$.name') AS name_json, JSON_EXTRACT(data, '$.address.city') AS city_json FROM users; -- Operator shorthand (5.7.8+): SELECT data->'$.name' AS name_json, data->>'$.name' AS name_text FROM users; -- Array index: SELECT JSON_EXTRACT(data, '$.tags[0]') AS first_tag FROM posts;

PostgreSQL

The -> operator returns a JSON value; ->> returns text. For nested paths use #> (JSON) and #>> (text) with an array of keys. jsonb columns support the same operators plus GIN-indexable containment operators (@>, <@, ?, ?|, ?&). json_extract_path() and jsonb_extract_path_text() are function equivalents.

-- Single key, JSON result: SELECT data->'name' AS name_json FROM users; -- Single key, text result: SELECT data->>'name' AS name_text FROM users; -- Nested path, JSON result: SELECT data#>'{address,city}' AS city_json FROM users; -- Nested path, text result: SELECT data#>>'{address,city}' AS city_text FROM users; -- Function form: SELECT jsonb_extract_path_text(data, 'address', 'city') AS city FROM users;

SQL Server

JSON_VALUE(col, path) extracts a scalar (returns NVARCHAR). JSON_QUERY(col, path) extracts an object or array (returns NVARCHAR containing JSON). Passing the wrong function for the value type returns NULL silently. No -> operators — all access is function-based with $.key path strings.

-- Scalar extraction: SELECT JSON_VALUE(data, '$.name') AS name, JSON_VALUE(data, '$.address.city') AS city FROM users; -- Object / array extraction: SELECT JSON_QUERY(data, '$.address') AS address_obj, JSON_QUERY(data, '$.tags') AS tags_arr FROM users; -- In WHERE clause: SELECT * FROM users WHERE JSON_VALUE(data, '$.active') = 'true';

Oracle

JSON_VALUE(col, path) extracts a scalar; JSON_QUERY(col, path) extracts objects or arrays. The RETURNING clause controls the output type. Error handling clauses (NULL ON ERROR, ERROR ON ERROR) prevent silent failures. Oracle 21c added JSON_TRANSFORM and dot-notation access on JSON type columns.

-- Scalar: SELECT JSON_VALUE(data, '$.name') AS name, JSON_VALUE(data, '$.score' RETURNING NUMBER) AS score FROM users; -- Object / array: SELECT JSON_QUERY(data, '$.address') AS address_obj, JSON_QUERY(data, '$.tags' WITH ARRAY WRAPPER) AS tags_arr FROM users; -- Dot notation (21c+ native JSON type): SELECT u.data.name.string() AS name FROM users u;

SQLite

json_extract(col, path) is the core function, available since 3.9.0. The -> and ->> operators (matching MySQL semantics) were added in SQLite 3.38.0 (2022-02-22). ->> always returns an SQL value (text/integer/real), never a JSON-encoded string.

-- Function form (3.9.0+): SELECT json_extract(data, '$.name') AS name, json_extract(data, '$.address.city') AS city FROM users; -- Operator form (3.38.0+): SELECT data->>'name' AS name_text, data->'$.address' AS address_json FROM users; -- Array element: SELECT json_extract(data, '$.tags[0]') AS first_tag FROM posts;