# **JSON_EXTRACT()** ## **Function Description** `JSON EXTRACT()` is a JSON query function that can be used to query JSON documents. - If you are following select as a column, it is recommended to use the functions [JSON_EXTRACT_STRING()](json_extract_string.md) and [JSON_EXTRACT_FLOAT64()](json_extract_float64.md); - If comparing in where conditions: - If type is json object comparison, use `JSON EXTRACT()` - If the type is a string type, use [JSON_EXTRACT_STRING()](json_extract_string.md); - If type is float or int, use [JSON_EXTRACT_FLOAT64()](json_extract_float64.md). ## **Grammar Structure** ```sql select json_extract(jsonDoc, pathExpression); ``` ## **Parameter definition** | Parameters | Description | | ---- | ---- | | jsonDoc | This is an expression containing JSON data. | | pathExpression | Indicates the path to access a value in a JSON document. The path starts with `$`, representing the root of the JSON document, followed by the dot number `.` and key name or access elements of the array with square brackets [ ]. | The path expression must start with the `$` character: - `.` followed by a key name, name the members in the object with the given key. Key names need to be included in quotes. - `[N]`: After selecting *path* of the array, name the value at position `N` in the array. The array position is an integer starting from zero. If the array is negative, an error will be reported. - The path can contain `*` or `**` wildcard: + `.[*]` calculates the values of all members in the JSON object. + `[*]` calculates the values of all elements in the JSON array. + `prefix**suffix`: Calculates all paths that start with a named prefix and end with a named suffix. - Paths (or data that do not exist) in the document are evaluated as `NULL`. The following set of JSON arrays: ```text [3, {"a": [5, 6], "b": 10}, [99, 100]] ``` - `$[0]` means 3. - `$[1]` means {"a": [5, 6], "b": 10}. - `$[2]` means [99, 100]. - `$[3]` is NULL (the array path starts with `$[0]`, while `$[3]` represents the fourth set of data, which does not exist). Since `$[1]` and `$[2]` are evaluated as non-scalar values, the expression can be nested. For example: - `$[1].a` means [5, 6]. - `$[1].a[1]` means 6. - `$[1].b` means 10. - `$[2][0]` means 99. The key names need to be double quoted in the path expression. `$` refers to this key value, and also requires double quotes: ```text {"a fish": "shark", "a bird": "sparrow"} ``` Both keys contain a space and must be enclosed in quotes: - `$."a fish"` means `shark`. - `$."a bird"` means `sparrow`. ## **Example** ```text mysql> select JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.*'); +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.*') | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | [1, 2, [3, 4, 5]] | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- mysql> SELECT JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.c[*]'); +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.c[*]') | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | [3, 4, 5] | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- mysql> select json_extract('{"a":{"q":[1,2,3]}}','$.a.q[1]'); +-----------------------------------------------------+ | json_extract({"a":{"q":[1,2,3]}}, $.a.q[1]) | +-----------------------------------------------------+ | 2 | +-----------------------------------------------------+ 1 row in set (0.00 sec) mysql> select JSON_EXTRACT('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b'); +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | JSON_EXTRACT('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b') | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | [null, 1, 2] | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- #In the following example, querying JSON values from columns will be shown: create table t1 (a json,b int); insert into t1(a,b) values ('{"a":1,"b":2,"c":3}',1); mysql> select json_extract(t1.a,'$.a') from t1 where t1.b=1; +------------------------------+ | json_extract(t1.a, $.a) | +------------------------------+ | 1 | +------------------------------+ 1 row in set (0.00 sec) insert into t1(a,b) values ('{"a":4,"b":5,"c":6}',2); mysql> select json_extract(t1.a,'$.b') from t1 where t1.b=2; +------------------------------+ | json_extract(t1.a, $.b) | +------------------------------+ | 5 | +------------------------------+ 1 row in set (0.00 sec) mysql> select json_extract(t1.a,'$.a') from t1; +------------------------------+ | json_extract(t1.a, $.a) | +------------------------------+ | 1 | | 4 | +------------------------------+ 2 rows in set (0.00 sec) insert into t1(a,b) values ('{"a":{"q":[1,2,3]}}',3); mysql> select json_extract(t1.a,'$.a.q[1]') from t1 where t1.b=3; +----------------------------------+ | json_extract(t1.a, $.a.q[1]) | +----------------------------------+ | 2 | +----------------------------------+ 1 row in set (0.01 sec) insert into t1(a,b) values ('[{"a":1,"b":2,"c":3},{"a":4,"b":5,"c":6}]',4); mysql> select json_extract(t1.a,'$[1].a') from t1 where t1.b=4; +----------------------------------+ | json_extract(t1.a, $[1].a) | +----------------------------------+ | 4 | +----------------------------------+ 1 row in set (0.00 sec) ```