获取json字段内容
mysql> SELECT c, JSON_EXTRACT(c, "$.id"), g
> FROM jemp
> WHERE JSON_ EXTRACT(c, "$.id") > 1
| {"id": "3", "name": "Barney"} | "3" | 3 |
| {"id": "4", "name": "Betty"} | "4" | 4 |
3 rows in set (0.00 sec)
mysql> SELECT c, c->"$.id", g
> FROM jemp
> WHERE c->"$.id" > 1
| {"id": "3", "name": "Barney"} | "3" | 3 |
| {"id": "4", "name": "Betty"} | "4" | 4 |
3 rows in set (0.00 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" : "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)