get_json_string,get_json_object

Analyzes and gets strings from the specified path (json_path) in a JSON string. If the format of json_string or json_path is wrong or if no matching value is found, this function will return NULL.

tip

All of the JSON functions and operators are listed in the navigation and on the overview page

Accelerate your queries with generated columns

The alias is get_json_object.

Syntax

VARCHAR get_json_string(VARCHAR json_str, VARCHAR json_path)

Parameters

• json_str: the JSON string. Supported data type is VARCHAR.
• json_path: the JSON path. Supported data type is VARCHAR. json_path starts with $ and uses . as the path separator. [ ] is used as the array subscripts which starts from 0. For example, $."my.key"[1] indicates to obtain the second value form element my.key.

Return value

Returns a value of the VARCHAR type. If no matching object is found, NULL is returned.

Examples

Example 1: Get the value whose key is k1.

MySQL > SELECT get_json_string('{"k1":"v1", "k2":"v2"}', "$.k1");
+---------------------------------------------------+
| get_json_string('{"k1":"v1", "k2":"v2"}', '$.k1') |
+---------------------------------------------------+
| v1                                                |
+---------------------------------------------------+

Example 2: Get the value whose key is a from the first element.

MySQL > SELECT get_json_object('[{"a":"123", "b": "456"},{"a":"23", "b": "56"}]', '$[0].a');
+------------------------------------------------------------------------------+
| get_json_object('[{"a":"123", "b": "456"},{"a":"23", "b": "56"}]', '$[0].a') |
+------------------------------------------------------------------------------+
| 123                                                                          |
+------------------------------------------------------------------------------+

Example 3: Get the second element in the array whose key is my.key

MySQL > SELECT get_json_string('{"k1":"v1", "my.key":["e1", "e2", "e3"]}', '$."my.key"[1]');
+------------------------------------------------------------------------------+
| get_json_string('{"k1":"v1", "my.key":["e1", "e2", "e3"]}', '$."my.key"[1]') |
+------------------------------------------------------------------------------+
| e2                                                                           |
+------------------------------------------------------------------------------+

Example 4: Get the first element in the array whose path is k1.key -> k2.

MySQL > SELECT get_json_string('{"k1.key":{"k2":["v1", "v2"]}}', '$."k1.key".k2[0]');
+-----------------------------------------------------------------------+
| get_json_string('{"k1.key":{"k2":["v1", "v2"]}}', '$."k1.key".k2[0]') |
+-----------------------------------------------------------------------+
| v1                                                                    |
+-----------------------------------------------------------------------+

Example 5: Get all values whose key is k1 from the array.

MySQL > SELECT get_json_string('[{"k1":"v1"}, {"k2":"v2"}, {"k1":"v3"}, {"k1":"v4"}]', '$.k1');
+---------------------------------------------------------------------------------+
| get_json_string('[{"k1":"v1"}, {"k2":"v2"}, {"k1":"v3"}, {"k1":"v4"}]', '$.k1') |
+---------------------------------------------------------------------------------+
| ["v1","v3","v4"]                                                                |
+---------------------------------------------------------------------------------+

keyword

GET_JSON_STRING,GET,JSON,STRING