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