Shareware software
->
База данных: MySQL
Topic
Syntax:
column->path
The -> operator serves as an alias for the JSON_EXTRACT() function when
used with two arguments, a column identifier on the left and a JSON
path on the right that is evaluated against the JSON document (the
column value). You can use such expressions in place of column
identifiers wherever they occur in SQL statements.
URL: https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html
Example
mysql> SELECT c, JSON_EXTRACT(c, "$.id"), g
> FROM jemp
> WHERE JSON_EXTRACT(c, "$.id") > 1
> ORDER BY JSON_EXTRACT(c, "$.name");
+-------------------------------+-----------+------+
| c | c->"$.id" | g |
+-------------------------------+-----------+------+
| {"id": "3", "name": "Barney"} | "3" | 3 |
| {"id": "4", "name": "Betty"} | "4" | 4 |
| {"id": "2", "name": "Wilma"} | "2" | 2 |
+-------------------------------+-----------+------+
3 rows in set (0.00 sec)
mysql> SELECT c, c->"$.id", g
> FROM jemp
> WHERE c->"$.id" > 1
> ORDER BY c->"$.name";
+-------------------------------+-----------+------+
| c | c->"$.id" | g |
+-------------------------------+-----------+------+
| {"id": "3", "name": "Barney"} | "3" | 3 |
| {"id": "4", "name": "Betty"} | "4" | 4 |
| {"id": "2", "name": "Wilma"} | "2" | 2 |
+-------------------------------+-----------+------+
3 rows in set (0.00 sec)
mysql> ALTER TABLE jemp ADD COLUMN n INT;
Query OK, 0 rows affected (0.68 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> UPDATE jemp SET n=1 WHERE c->"$.id" = "4";
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT c, c->"$.id", g, n
> FROM jemp
> WHERE JSON_EXTRACT(c, "$.id") > 1
> ORDER BY c->"$.name";
+-------------------------------+-----------+------+------+
| c | c->"$.id" | g | n |
+-------------------------------+-----------+------+------+
| {"id": "3", "name": "Barney"} | "3" | 3 | NULL |
| {"id": "4", "name": "Betty"} | "4" | 4 | 1 |
| {"id": "2", "name": "Wilma"} | "2" | 2 | NULL |
+-------------------------------+-----------+------+------+
3 rows in set (0.00 sec)
mysql> DELETE FROM jemp WHERE c->"$.id" = "4";
Query OK, 1 row affected (0.04 sec)
mysql> SELECT c, c->"$.id", g, n
> FROM jemp
> WHERE JSON_EXTRACT(c, "$.id") > 1
> ORDER BY c->"$.name";
+-------------------------------+-----------+------+------+
| c | c->"$.id" | g | n |
+-------------------------------+-----------+------+------+
| {"id": "3", "name": "Barney"} | "3" | 3 | NULL |
| {"id": "2", "name": "Wilma"} | "2" | 2 | NULL |
+-------------------------------+-----------+------+------+
2 rows in set (0.00 sec)