JSON_VALUE() finds a specified scalar JSON value in JSON data and returns it as a SQL value.
Examples
I will use the mysql_x example database data for examples. So let us start with getting the life expectancy data.
SELECT JSON_EXTRACT(doc, "$.demographics.LifeExpectancy") AS raw
FROM countryinfo
LIMIT 4;
+--------------------+
| raw |
+--------------------+
| 78.4000015258789 |
| 45.900001525878906 |
| 38.29999923706055 |
| 76.0999984741211 |
+--------------------+
That is great information if not exactly human eye friendly.
We can use JSON_VALUE() to make it a little easier for humans
SELECT
JSON_VALUE(doc, "$.demographics.LifeExpectancy" RETURNING DECIMAL(6,2)) AS trimmed FROM countryinfo
LIMIT 4;
+---------+
| trimmed |
+---------+
| 78.40 |
| 45.90 |
| 38.30 |
| 76.10 |
+---------+
And it can be very useful in a WHERE clause. In this example there is no RETURNING clause.
SELECT doc->"$.Name"
FROM countryinfo
WHERE JSON_VALUE(doc, "$.demographics.LifeExpectancy"
RETURNING DECIMAL(6,3)) > 80.1;
+---------------+
| doc->"$.Name" |
+---------------+
| "Andorra" |
| "Japan" |
| "Macao" |
| "San Marino" |
+---------------+
The optional RETURN clause will cast your data as FLOAT, DOUBLE, DECIMAL, SIGNED,
UNSIGNED, DATE, TIME, DATETIME, CHAR, or JSON.
There are even on empty and on error clauses.
SELECT JSON_VALUE('{firstname:"John"}', '$.lastname'
DEFAULT 'No last name found' ON ERROR) AS "Last Name";
+--------------------+
| Last Name |
+--------------------+
| No last name found |
+--------------------+
And of course it can be used to help define indexes.
CREATE TABLE xdemo (j JSON,
x INTEGER,
key((json_value(j, '$.id')))
);
