JSON_EXTRACT_FLOAT64()

Function Description

JSON_EXTRACT_FLOAT64() The value used to extract the 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_float64(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 table student(n1 int,n2 json);
insert into student values
    (1,'{"name": "tom", "age": 18, "score": 90,"gender": "male"}'),
    (2,'{"name": "bob", "age": 20, "score": 80,"gender": "male"}'),
    (3,'{"name": "jane", "age": 17, "score": 95,"gender": "female"}'),
    (4,'{"name": "lily", "age": 19, "score": 79,"gender": "female"}');

mysql> select n1 from student where json_extract_float64(n2,'$.age')=19;
+------+
| n1 |
+------+
| 4 |
+------+
1 row in set (0.00 sec)

select json_extract_float64(n2,'$.age')=19;