JSON_EXTRACT_<type>

Extracts data from JSON, optionally based on a path specification.

When used with JSON object or array and a path specification, extracts data from the JSON object or array that matches the path specification. Will return NULL if the path is not matched or if either of the arguments are NULL.

When used with a JSON argument and no path specification, extracts data from the JSON to a value of <type>. With this usage, it is possible to extract from JSON string, numeric, boolean, and NULL values in addition to extracting from JSON objects and arrays.

The following JSON_EXTRACT_<type> functions are supported:

JSON_EXTRACT_DOUBLE is for all numeric data (equivalent to the ::% JSON operator).
JSON_EXTRACT_STRING is for all text and binary types (equivalent to the ::$ JSON operator). The result collation is controlled by the json_extract_string_collation engine variable. Refer to Sync Variables Lists for more information.
JSON_EXTRACT_JSON is for all valid JSON types, including true, false, JSON maps, and JSON lists.
JSON_EXTRACT_BIGINT is for all BIGINT types. Other data types are handled as follows: Booleans (true and false) become 1 and 0, respectively. Floats are rounded as follows: 1.4 becomes 1, while 1.5 becomes 2. For all other data types, 0 is returned.

Note

For more information on JSON operators ::, ::$, and ::%, see Accessing Fields in a JSON Object. Also, see Character Encodings and Collation for information on overriding collation settings.

Syntax

SQL

JSON_EXTRACT_<type>(<json>, <keypath> [, ]);

JSON_EXTRACT_<type>(<json>);

Arguments

json: valid JSON, or the name of a JSON column.
keypath: (optional) The path specification; a comma-separated list of object keys or zero-indexed array positions.

Return Value

When JSON_EXTRACT_<type> is used with a JSON object or array literal and a path specification, the return values are as listed below. Refer to JSON_EXTRACT_<type> with Path Specification for more details.

SQL NULL if value is JSON NULL (such as {"a": null}), or if keyname does not exist.
The extracted element if <json> is a valid JSON object or array and the path specified by the list of <keypath>s exists in <json>.
SQL NULL if <json> is not a valid JSON object or array or if the path specified by the list of <keypaths> does not exist in <json>.
SQL NULL if <json> is SQL NULL, an empty string, a JSON string, a JSON number, a JSON boolean value, or an empty JSON object or array.
JSON null for JSON_EXTRACT_JSON if the value is JSON null (such as {"a": null}).
SQL NULL for JSON_EXTRACT_STRING if the value is JSON null (such as {"a": null}).
0 for JSON_EXTRACT_BIGINT and JSON_EXTRACT_DOUBLE if the value is JSON null (such as {"a": null}).

Refer to JSON_EXTRACT_<type> - JSON Argument Only the return values of JSON_EXTRACT_<type> when used with a JSON argument only.

JSON Computed Column Optimization

JSON computed column optimization allows the engine to parse JSON strings once per row and extract fields for computed columns instead of parsing the strings multiple times. This optimization is enabled by setting optimize_json_computed_column to true.

Consider a table with a JSON column and multiple computed columns whose values are created by extracting fields from the JSON column. With this optimization, the engine parses the JSON column only once and uses the result to form each computed column value. This reduces the number of times the JSON is parsed and can significantly improve performance. Refer to JSON Computed Column Optimization Examples for more examples.

Remarks

Performance

This optimization works best when there are a high number of computed columns and shorter keypaths. That is, the optimization scales well as the number of computed columns increases and performs less well as keys get longer and more deeply nested.
For best performance use short JSON keys, limit the number of keys in key paths, and avoid accessing non-leaf fields.
For deeply nested JSON data, set optimize_json_computed_column to false as performance of this optimization degrades with deep nesting. If the key length exceeds the default threshold of 1 MB, the optimized parsing is turned off.
For extremely small JSON, disable the optimization by setting optimize_json_computed_column to false.

Usage

The optimization triggers when a JSON column is used at least twice across all computed column expressions within a single row. It applies on a per-JSON-column basis for selective optimization.
The first argument of the computed column expression must be a JSON_EXTRACT_<type> expression that references a JSON column.
This optimization is available only in INSERT and SELECT statements.

Impact of Disabling `optimize_json_computed_column`

When optimize_json_computed_column is set to false, the system parses the JSON string separately for each computed column expression. Accessing the same JSON data multiple times within a row results in redundant parsing, which impacts performance. This slows query execution and affects write operations.

JSON_EXTRACT_<type> with Path Specification

The following table summarizes the functionality and return types of JSON_EXTRACT_<type> when both a JSON object or array literal and a path specification are provided as arguments.

`<json>`	Function	Return	Comments
JSON Object or Array Literal	`JSON_EXTRACT_JSON(<json>, <keypath> [,...])`	json or SQL `NULL`	Extracts the JSON value found at the path (`<keypath> [,...]`). Value returned as JSON. If the extracted object is a JSON string, quotes are not removed. Returns SQL `NULL` if keypath is not found.
	`JSON_EXTRACT_STRING(<json>, <keypath> [,...])`	string or SQL `NULL`	Extracts the JSON value found at the path (`<keypath> [,...]`). Value returned as a string. If the extracted object is a JSON string, quotes are removed. Returns SQL `NULL` if keypath is not found.
	`JSON_EXTRACT_BIGINT(<json>, <keypath> [,...])`	integer or 0	If the extracted value is a JSON string, extracts a valid numeric prefix and rounds to an Integer. Value returned as a BIGINT. Otherwise, returns 0.
	`JSON_EXTRACT_DOUBLE(<json>, <keypath> [,...])`	double or 0	If the extracted value is a JSON string, extracts a valid numeric prefix. Value returned as DOUBLE. Otherwise, returns 0.

JSON_EXTRACT_<type> - JSON Argument Only

The following table summarizes the functionality and return values of JSON_EXTRACT_<type> over a JSON argument only.

For all functions, the return value matches the type of the function. JSON_EXTRACT_JSON returns a JSON value, JSON_EXTRACT_STRING returns a string value, and so on.

<json>	Function	Return	Notes
SQL `NULL`	`JSON_EXTRACT_<type>(NULL)`	SQL `NULL`
Empty string	`JSON_EXTRACT_<type>('')`	SQL `NULL`
JSON `null`	`JSON_EXTRACT_JSON('null')`	JSON `null`	Returns JSON `null`
	`JSON_EXTRACT_STRING('null')` `JSON_EXTRACT_BIGINT('null')` `JSON_EXTRACT_DOUBLE('null')`	SQL `NULL`
Empty JSON Object	`JSON_EXTRACT_JSON('{}')`	`{}`	Returns JSON value.
	`JSON_EXTRACT_STRING('{}')`	`{}`	Returns string value.
	`JSON_EXTRACT_BIGINT('{}')` `JSON_EXTRACT_DOUBLE('{}')`	`0`	Return value matches type of function.
JSON boolean value	`JSON_EXTRACT_JSON('true')` `JSON_EXTRACT_JSON('false')`	`true` `false`	Returns JSON value.
	`JSON_EXTRACT_STRING('true')` `JSON_EXTRACT_STRING('false')`	`true` `false`	Returns string value.
	`JSON_EXTRACT_BIGINT('true')` `JSON_EXTRACT_DOUBLE('true')` `JSON_EXTRACT_BIGINT('false')` `JSON_EXTRACT_DOUBLE('false')`	`1` `0`	Returns 1 for true, 0 for false. Return value matches type of function.
JSON string	`JSON_EXTRACT_JSON('"<string>"')`	"<string>"	Does not remove quotes.
	`JSON_EXTRACT_STRING('"<string>"')`	<string >	Removes quotes.
	`JSON_EXTRACT_BIGINT('"<string>"')` `JSON_EXTRACT_DOUBLE('"<string>"')`	numeric prefix or `0`	If string has a valid numeric prefix, returns the numeric prefix, rounded appropriately. Returns 0 if the string does not have a valid numeric prefix.
JSON number	`JSON_EXTRACT_JSON('<numeric>')`	numeric value	Returns JSON value.
	`JSON_EXTRACT_STRING('<numeric>')`	numeric value	Returns string value.
	`JSON_EXTRACT_BIGINT('<numeric>')`	rounded numeric value	Returns BIGINT.
	`JSON_EXTRACT_DOUBLE('<numeric>')`	numeric value	Returns DOUBLE.
JSON object	`JSON_EXTRACT_JSON(<json object>)` `JSON_EXTRACT_STRING(<json object>)`	<json object>	Extracts full object.
	`JSON_EXTRACT_BIGINT(<json object>)` `JSON_EXTRACT_DOUBLE(<json object>)`	0	Return value matches type of function.
JSON array	`JSON_EXTRACT_JSON(<json array>)` `JSON_EXTRACT_STRING(<json array˘>)`	<json array>	Extracts full array.
	`JSON_EXTRACT_BIGINT(<json array>)` `JSON_EXTRACT_DOUBLE(<json array>)`	`0`	Return value matches type of function.
Invalid JSON	`JSON_EXTRACT_JSON(<invalid json>)` `JSON_EXTRACT_STRING(<invalid json>)`	SQL `NULL`
	`JSON_EXTRACT_BIGINT(<invalid json>)` `JSON_EXTRACT_DOUBLE(<invalid json>)`	`0`

Multi-Argument Examples

Example 1

Extracts the value for beta.

SQL

SELECT JSON_EXTRACT_DOUBLE('{"alpha":1, "beta":2, "gamma": [3,4,5]}', 'beta') 
AS get_beta;

+----------+
| get_beta |
+----------+
|        2 |
+----------+

Example 2

Extracts value for 1 from an array.

SQL

SELECT JSON_EXTRACT_DOUBLE('[3,4,5]', 1) AS get_result;

+------------+
| get_result |
+------------+
|          4 |
+------------+

Note

Since JSON uses zero-indexed array positions, extracting with the path 0 will extract the value 3 and the path 1 will extract the value 4 as in the example above.

Example 3

Extracts the value for the path gamma.

SQL

SELECT JSON_EXTRACT_JSON('{"alpha":1, "beta":2, "gamma": [3,4,5]}', 'gamma') 
AS get_gamma;

+-----------+
| get_gamma |
+-----------+
| [3,4,5]   |
+-----------+

Example 4

Extracts the value for the path gamma.1. Since arrays use zero-based indexing, the function will extract the number in the first position of the array [3,4,5] which is 4 in this example.

SQL

SELECT JSON_EXTRACT_JSON('{"alpha":1, "beta":2, "gamma": [3,4,5]}', 'gamma', 1) 
AS get_gamma;

+-----------+
| get_gamma |
+-----------+
| 4         |
+-----------+

Example 5

The result in the following example is NULL as zeta has no value.

SQL

SELECT JSON_EXTRACT_JSON('{"alpha":1, "beta":2, "gamma": [3,4,5]}', 'zeta') 
AS get_zeta;

+-----------+
| get_zeta  |
+-----------+
| NULL      |
+-----------+

Example 6

The result in the next example is rounded up to the next whole integer. If the value had been 1.25 the result would have been 1.

SQL

SELECT JSON_EXTRACT_BIGINT('{"alpha":1, "beta":2, "gamma": [3,4,5], "delta":1.5}', 'delta') 
AS get_delta;

+-----------+
| get_delta |
+-----------+
| 2         |
+-----------+

Example 7

The value for the path gamma is an array so the entire array is returned as a string value.

SQL

SELECT JSON_EXTRACT_STRING('{"alpha":1, "beta":2, "gamma": [3,4,5]}', 'gamma') 
AS get_gamma;

+-----------+
| get_gamma |
+-----------+
| [3,4,5]   |
+-----------+

Example 8

Returns the value for the path gamma which is a non-numeric string in this example.

SQL

SELECT JSON_EXTRACT_STRING('{"alpha":1, "beta":2, "gamma": "A string"}', 'gamma') 
AS get_gamma;

+-----------+
| get_gamma |
+-----------+
| A string  |
+-----------+

Single Argument Examples

Example 1 - JSON_EXTRACT_JSON

Extract from a JSON string with " " quotes. The JSON_EXTRACT_JSON function does not remove the " " quotes from the string.

SQL

SELECT JSON_EXTRACT_JSON('"a string"');

+---------------------------------------------+
| JSON_EXTRACT_JSON('"a string"')             |
+---------------------------------------------+
|                         "a string"          |
+---------------------------------------------+

Extract from a string without " " quotes.

SQL

SELECT JSON_EXTRACT_JSON('a string');

+----------------------------------------+
| JSON_EXTRACT_JSON('a string')          |
+----------------------------------------+
|                         NULL           |
+----------------------------------------+

The JSON_EXTRACT_JSON function with one argument will return the JSON input if the input is valid JSON or SQL NULL if the input is invalid JSON.

Extract from a JSON array. Will extract the entire array.

SQL

SELECT JSON_EXTRACT_JSON('["array element"]');

+----------------------------------------+
| JSON_EXTRACT_JSON('["array element"]') |
+----------------------------------------+
|                   ["array element"]    |
+----------------------------------------+

Extract from a JSON object with two key-value pairs, for which the values are arrays with a single element. The entire JSON object will be extracted.

SQL

SELECT JSON_EXTRACT_JSON('{"key2":[2],"key3":[3]}');

+----------------------------------------------+
| JSON_EXTRACT_JSON('{"key2":[2],"key3":[3]}') |
+----------------------------------------------+
|                     {"key2":[2],"key3":[3]}  |
+----------------------------------------------+

Example 2 - JSON_EXTRACT_STRING

Extract from a string. The JSON_EXTRACT_STRING function removes the " " quotes from the string.

SQL

SELECT JSON_EXTRACT_STRING('"a string"');

+----------------------------------+
| JSON_EXTRACT_STRING('"a string"')|
+----------------------------------+
|                    a string      |
+----------------------------------+

The JSON_EXTRACT_STRING function will return the JSON input as a string if the input is valid JSON or SQL NULL if the input is invalid JSON.

Extract from a JSON array with one element which is a string. Will extract the entire array.

SQL

SELECT JSON_EXTRACT_STRING('["array element"]');

+------------------------------------------+
| JSON_EXTRACT_STRING('["array element"]') |
+------------------------------------------+
|                     ["array element"]    |
+------------------------------------------+

Extract from a JSON object with two key-value pairs, for which the values are arrays with a single element. The entire JSON object will be extracted.

SQL

SELECT JSON_EXTRACT_STRING('{"key2":[2],"key3":[3]}');

+------------------------------------------------+
| JSON_EXTRACT_STRING('{"key2":[2],"key3":[3]}') |
+------------------------------------------------+
|                    {"key2":[2],"key3":[3]}     |
+------------------------------------------------+

Example 3 - JSON_EXTRACT_BIGINT

Extract from a JSON double value. Rounds to the nearest integer.

SQL

SELECT JSON_EXTRACT_BIGINT('3.14');

+-------------------------------+
| JSON_EXTRACT_BIGINT('3.14')   |
+-------------------------------+
|                          3    |
+-------------------------------+

Extract from a JSON string with a numeric prefix. Extracts a valid numeric prefix from the string and rounds to the nearest integer.

SQL

SELECT JSON_EXTRACT_BIGINT('"12.3a"');

+-------------------------------+
| JSON_EXTRACT_BIGINT('"12.3a"')|
+-------------------------------+
|                       12      |
+-------------------------------+

Example 4 - JSON_EXTRACT_DOUBLE

Extract from a JSON double value.

SQL

SELECT JSON_EXTRACT_DOUBLE('3.14');

+-------------------------------+
| JSON_EXTRACT_DOUBLE('3.14')   |
+-------------------------------+
|                          3.14 |
+-------------------------------+

Extract from a JSON string containing a double value. Extracts a valid numeric prefix from the string.

SQL

SELECT JSON_EXTRACT_DOUBLE('"12.3a"');

+-------------------------------+
| JSON_EXTRACT_DOUBLE('"12.3a"')|
+-------------------------------+
|                       12.3    |
+-------------------------------+

JSON Computed Column Optimization Examples

Example 1

Uses the JSON computed column optimization to compute values from multiple JSON keys within a single row.

SQL

CREATE TABLE computed_table (
   json_data JSON,
   sum_ab AS JSON_EXTRACT_BIGINT(json_data, 'a') + 
   JSON_EXTRACT_BIGINT(json_data, 'b') PERSISTED BIGINT,
   value_c AS JSON_EXTRACT_BIGINT(json_data, 'c') PERSISTED BIGINT
);

INSERT INTO computed_table (json_data) VALUES
   ('{"a": 1, "b": 2, "c": 3}'),
   ('{"a": 4, "b": 5, "c": 6}'),
   ('{"a": 10, "b": 20, "c": 30}');

SELECT sum_ab AS total_a_b, value_c AS extracted_c FROM computed_table;

+-----------+-------------+
| total_a_b | extracted_c |
+-----------+-------------+
|        30 |          30 |
|         9 |           6 |
|         3 |           3 |
+-----------+-------------+

Example 2 - Non Overlapping JSON Data

Uses the JSON computed column optimization to extract values from non-overlapping JSON data, where each computed column references a different key in the JSON.

SQL

CREATE TABLE novels (
   novel_data JSON,
   novel_name AS novel_data::name PERSISTED LONGTEXT,
   novel_author AS novel_data::author PERSISTED LONGTEXT
);

INSERT INTO novels (novel_data) VALUES
   ('{"name": "To Kill a Mockingbird", "author": "Harper Lee"}'),
   ('{"name": "1984", "author": "George Orwell"}'),
   ('{"name": "Pride and Prejudice", "author": "Jane Austen"}'),
   ('{"name": "The Great Gatsby", "author": "F. Scott Fitzgerald"}');

SELECT novel_name, novel_author FROM novels;

+-------------------------+-----------------------+
| novel_name              | novel_author          |
+-------------------------+-----------------------+
| "The Great Gatsby"      | "F. Scott Fitzgerald" |
| "To Kill a Mockingbird" | "Harper Lee"          |
| "Pride and Prejudice"   | "Jane Austen"         |
| "1984"                  | "George Orwell"       |
+-------------------------+-----------------------+

Example 3 - Nested JSON Data

Uses the JSON computed column optimization to handle nested JSON data, extract a nested array from a key in the JSON column, and store the result in a computed column.

SQL

CREATE TABLE employee (
   employee_data JSON,
   employee_info AS employee_data::a PERSISTED LONGTEXT
);

INSERT INTO employee (employee_data) VALUES
   ('{"a": [{"name": "Alice Johnson", "designation": "Software Engineer"}, {"name": "Bob Smith", "designation": "Project Manager"}]}'),
   ('{"a": [{"name": "Charlie Brown", "designation": "Data Analyst"}, {"name": "Diana Prince", "designation": "UX Designer"}]}');

SELECT employee_info AS employee_details FROM employee;

+-------------------------------------------------------------------------------------------------------------------+
| employee_details                                                                                                  |
+-------------------------------------------------------------------------------------------------------------------+
| [{"designation":"Data Analyst","name":"Charlie Brown"},{"designation":"UX Designer","name":"Diana Prince"}]       |
| [{"designation":"Software Engineer","name":"Alice Johnson"},{"designation":"Project Manager","name":"Bob Smith"}] |
+-------------------------------------------------------------------------------------------------------------------+

Last modified: November 18, 2024

JSON_EXTRACT_<type>

On this page

Syntax

Arguments

Return Value

JSON Computed Column Optimization

Remarks

Performance

Usage

Impact of Disabling `optimize_json_computed_column`

JSON_EXTRACT_<type> with Path Specification

JSON_EXTRACT_<type> - JSON Argument Only

Multi-Argument Examples

Example 1

Example 2

Example 3

Example 4

Example 5

Example 6

Example 7

Example 8

Single Argument Examples

Example 1 - JSON_EXTRACT_JSON

Example 2 - JSON_EXTRACT_STRING

Example 3 - JSON_EXTRACT_BIGINT

Example 4 - JSON_EXTRACT_DOUBLE

JSON Computed Column Optimization Examples

Example 1

Example 2 - Non Overlapping JSON Data

Example 3 - Nested JSON Data

Was this article helpful?

On this page

Was this article helpful?

JSON_EXTRACT_<type>

On this page

Syntax

Arguments

Return Value

JSON Computed Column Optimization

Remarks

Performance

Usage

Impact of Disabling optimize_json_computed_column

JSON_EXTRACT_<type> with Path Specification

JSON_EXTRACT_<type> - JSON Argument Only

Multi-Argument Examples

Example 1

Example 2

Example 3

Example 4

Example 5

Example 6

Example 7

Example 8

Single Argument Examples

Example 1 - JSON_EXTRACT_JSON

Example 2 - JSON_EXTRACT_STRING

Example 3 - JSON_EXTRACT_BIGINT

Example 4 - JSON_EXTRACT_DOUBLE

JSON Computed Column Optimization Examples

Example 1

Example 2 - Non Overlapping JSON Data

Example 3 - Nested JSON Data

Was this article helpful?

On this page

Was this article helpful?

Impact of Disabling `optimize_json_computed_column`