JSON_EXTRACT_STRING()

Function Description

JSON_EXTRACT_STRING() is used to extract the string value of the specified path from JSON data.

If you compare in where conditions:

• If type is json object comparison, use JSON EXTRACT();
• If the type is a string type, use JSON_EXTRACT_STRING();
• If type is float or int, use JSON_EXTRACT_FLOAT64().

Grammar Structure

select col_name from tab_name where json_extract_string(jsonDoc, pathExpression)= number;

Parameter definition

Parameters	Description
jsonDoc	This is a column or expression containing JSON data.
pathExpression	Indicates the path to access a value in a JSON document. Only one path can be received at a time. 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 [ ].
number	Specifies the value to be extracted in JSON data, which is a numerical type.

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 double 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.

Example

-- Create a test table
CREATE TABLE student(
    id INT,
    info JSON
);

-- Insert test data
INSERT INTO student VALUES
    (1, '{"name": "tom", "age": 18, "scores": [85, 90, 78]}'),
    (2, '{"name": "bob", "age": 20, "scores": [75, 82, 91]}');

-- Extract string value
mysql> SELECT id FROM student WHERE json_extract_string(info, '$.name') = 'tom';
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.01 sec)

-- The non-existent path returns NULL
mysql> SELECT json_extract_string(info, '$.address') FROM student;
+---------------------------------------------+
| json_extract_string(info, $.address) |
+---------------------------------------------+
| NULL |
| NULL |
+---------------------------------------------+
2 rows in set (0.00 sec)