Skip to content

JSON_EXTRACT()

Function Description

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

Grammar Structure

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:

[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:

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