Skip to content

JSON_EXTRACT()

Function description

JSON EXTRACT() is a JSON query function that can be used to query JSON documents.

-If used as a column after select, it is recommended to use the functions JSON_EXTRACT_STRING() and JSON_EXTRACT_FLOAT64();

-If comparing in a where condition: -If the type is json object comparison, use JSON EXTRACT() -If the type is a string type, use JSON_EXTRACT_STRING(); -If the type is float or int, use JSON_EXTRACT_FLOAT64().

Grammar structure

select json_extract(jsonDoc, pathExpression);

Parameter explanation

Parameters Description
jsonDoc This is an expression containing JSON data.
pathExpression Represents the path to access a value in the JSON document. The path starts with $, which represents the root of the JSON document, and can be followed by a period . and a key name or square brackets [ ] to access the elements of the array.

Path expressions must start with the $ character:

  • . followed by a key name names the member in the object using the given key. Key names need to be enclosed in quotes.

  • [N]: After selecting the pathof the array, name the value at position N in the array. Array positions are zero-based integers. If the array is negative, an error is reported.

  • The path can contain the * or ** wildcard characters:

    • .[*] Computes the values ​​of all members in a JSON object.

    • [*] Computes the values ​​of all elements in a JSON array.

    • prefix**suffix: Count all paths starting with a named prefix and ending with a named suffix.

  • Paths that do not exist in the document (or data that does not exist) evaluate to NULL.

The following set of JSON arrays:

[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 from $[0], and $[3] represents the fourth group of data, which does not exist).

Since $[1] and $[2] evaluate to non-scalar values, expressions can be nested. For example:

  • $[1].a means [5, 6].

  • $[1].a[1] means 6.

  • $[1].b means 10.

  • $[2][0] means 99.

Key names require double quotes in path expressions. $ quotes this key value and also requires double quotes:

{"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

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 a column 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)