mysql 查询JSON类型数据

来源：互联网发布：bf风格的淘宝店编辑：程序博客网时间：2024/06/05 22:48

获取json字段内容

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

阅读全文

0 0