mysql -->Searching and Modifying JSON Values
Searching and Modifying JSON Values
A JSON path expression selects a value within a JSON document.
Path expressions are useful with functions that extract parts of or modify a JSON document, to specify where within that document to operate. For example, the following query extracts from a JSON document the value of the member with the name key:
mysql> SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name');
+---------------------------------------------------------+
| JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name') |
+---------------------------------------------------------+
| "Aztalan" |
+---------------------------------------------------------+Path syntax uses a leading $ character to represent the JSON document under consideration, optionally followed by selectors that indicate successively more specific parts of the document:
-
A period followed by a key name names the member in an object with the given key. The key name must be specified within double quotation marks if the name without quotes is not legal within path expressions (for example, if it contains a space).
-
[appended to aN]paththat selects an array names the value at positionNwithin the array. Array positions are integers beginning with zero. Ifpathdoes not select an array value,path[0] evaluates to the same value aspath:mysql> SELECT JSON_SET('"x"', '$[0]', 'a'); +------------------------------+ | JSON_SET('"x"', '$[0]', 'a') | +------------------------------+ | "a" | +------------------------------+ 1 row in set (0.00 sec) -
[specifies a subset or range of array values starting with the value at positionMtoN]M, and ending with the value at positionN.lastis supported as a synonym for the index of the rightmost array element. Relative addressing of array elements is also supported. Ifpathdoes not select an array value,path[last] evaluates to the same value aspath, as shown later in this section (see Rightmost array element). -
Paths can contain
*or**wildcards:-
.[*]evaluates to the values of all members in a JSON object. -
[*]evaluates to the values of all elements in a JSON array. -
prefix**suffix
-
-
A path that does not exist in the document (evaluates to nonexistent data) evaluates to
NULL.
Let $ refer to this JSON array with three elements:
[3, {"a": [5, 6], "b": 10}, [99, 100]]Then:
-
$[0]evaluates to3. -
$[1]evaluates to{"a": [5, 6], "b": 10}. -
$[2]evaluates to[99, 100]. -
$[3]evaluates toNULL(it refers to the fourth array element, which does not exist).
Because $[1] and $[2] evaluate to nonscalar values, they can be used as the basis for more-specific path expressions that select nested values. Examples:
-
$[1].aevaluates to[5, 6]. -
$[1].a[1]evaluates to6. -
$[1].bevaluates to10. -
$[2][0]evaluates to99.
As mentioned previously, path components that name keys must be quoted if the unquoted key name is not legal in path expressions. Let $ refer to this value:
{"a fish": "shark", "a bird": "sparrow"}The keys both contain a space and must be quoted:
-
$."a fish"evaluates toshark. -
$."a bird"evaluates tosparrow.
Paths that use wildcards evaluate to an array that can contain multiple values:
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[*]');
+-----------------------------------