Bug #87757 FR: Shorthand update for JSON_SET or JSON_REPLACE
Submitted: 14 Sep 2017 13:15 Modified: 20 Sep 2017 5:37
Reporter: Morgan Tocker Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: JSON Severity:S4 (Feature request)
Version:8.0.3 OS:Any
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D5 (Feature request)

[14 Sep 2017 13:15] Morgan Tocker
Description:
It would be nice to be able to partial-update JSON using the shorthand json operator (->)

How to repeat:
mysql [localhost] {msandbox} (mybookings) > SELECT doc->"$.properties.accessible" FROM seats WHERE id = 1;
+--------------------------------+
| doc->"$.properties.accessible" |
+--------------------------------+
| 1                              |
+--------------------------------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (mybookings) > UPDATE seats SET doc->"$.properties.accessible" = 0 WHERE id = 1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '->"$.properties.accessible" = 0 WHERE id = 1' at line 1

mysql [localhost] {msandbox} (mybookings) > UPDATE seats SET doc = JSON_REPLACE(doc, "$.properties.accessible", 0) WHERE id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Suggested fix:
I am not sure if semantically JSON_REPLACE or JSON_SET makes more sense.  It is possible to have ->> and -> do one or the other.
[20 Sep 2017 5:37] Umesh Shastry
Hello Morgan,

Thank you for the feature request!

Thanks,
Umesh