| Bug #78736 | Shorthand JSON_UNQUOTE(JSON_EXTRACT(..)) operator | ||
|---|---|---|---|
| Submitted: | 7 Oct 2015 14:18 | Modified: | 2 May 2016 8:29 |
| Reporter: | Morgan Tocker | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: JSON | Severity: | S4 (Feature request) |
| Version: | 5.7 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[12 Oct 2015 4:18]
MySQL Verification Team
Hello Morgan, Thank you for the feature request! Regards, Umesh
[2 May 2016 8:29]
Jon Stephens
Fixed in 5.7.13 / 5.8.0.
[2 May 2016 10:06]
Jon Stephens
Documented feature addition in the 5.7 Manual, and in the 5.7.13 changelog as follows:
This release adds an unquoting extraction operator ->>,
sometimes also referred to as an inline path operator,
for use with JSON documents stored in MySQL. The new
operator is similar to the -> operator, but performs JSON
unquoting of the value as well. For a JSON column mycol
and JSON path expression mypath, the following three
expressions are equivalent:
+ JSON_UNQUOTE( JSON_EXTRACT(mycol, "$.mypath") )
+ JSON_UNQUOTE(mycol->"$.mypath")
+ mycol->>"$.mypath"
The ->> operator can be used in SQL statements wherever
JSON_UNQUOTE(JSON_EXTRACT()) would be allowed. This
includes (but is not limited to) SELECT lists, WHERE and
HAVING clauses, and ORDER BY and GROUP BY clauses.
For more information, see Functions That Search JSON
Values
(http://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html),
and JSON Path Syntax
(http://dev.mysql.com/doc/refman/5.7/en/json-path-syntax.html).
Description: MySQL 5.7.9 added a shorthand JSON extract operator - which I think is awesome! What I would like to see added is a shorthand JSON_UNQUOTE(JSON_EXTRACT(..)) operator, since I 99% of the time use the two together. How to repeat: CREATE TABLE employees ( data JSON, name VARCHAR(30) AS (JSON_EXTRACT(data, "$.name")) VIRTUAL ); INSERT INTO employees (data) VALUES('{"id": 1, "name": "Jane"}'); INSERT INTO employees (data) VALUES('{"id": 2, "name": "Joe"}'); mysql> SELECT data->"$.name", data-->"$.name" from employees; +----------------+-----------------+ | data->"$.name" | data-->"$.name" | +----------------+-----------------+ | "Jane" | Jane | | "Joe" | Joe | +----------------+-----------------+ 2 rows in set (0.00 sec) Suggested fix: Suggesting use of an extra dash (-->) as it feels natural.