Mysql json数组解析方法

一、背景

在表job_position需要对json数组进行解析，查找json数组对象中的数据

 首先想到查找mysql的关于json的函数，这边做个记录。

二、解决方案

2.1 JSON_EXTRACT(json字段，'$.属性名称')

需要了解函数 JSON_EXTRACT(json_doc, path[, path] ...) 

 从json中提取数据，JSON_EXTRACT(json字段，'$.属性名称')，解析正常则返回对应键的值，否则返回null值

比如（官方例子）

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)

随便写的例子

SELECT JSON_EXTRACT('{"name":"zhangsan","userCode":"oa123456"}','$.name');

 那对于json数组的解析呢？？？

比如json数组 [{"name":"zhangsan","userCode":"oa123456"},{"name":"lisi","userCode":"oa222222"}]

用上面的方式肯定不行

 ----------让我想想-----

2.2 JSON_EXTRACT(json数据, '$[数组下标].json属性名称')

使用 JSON_EXTRACT(json数据, '$[数组下标].json属性名称')

例子如下

SELECT JSON_EXTRACT(' [{"name":"zhangsan","userCode":"oa123456"},{"name":"lisi","userCode":"oa222222"}]','$[0].name') NAME;

 如果是 JSON_EXTRACT(json数据, '$[*].json属性名称') 则会返回数组

SELECT JSON_EXTRACT(' [{"name":"zhangsan","userCode":"oa123456"},{"name":"lisi","userCode":"oa222222"}]','$[*].name') NAME;

3.mysql中数组包含某个元素的方法

SELECT '北京' member of('["天津","北京"]')

结果存在则返回1，否则返回0

 

 

查询某个城市是否包含在work_address 工作地点的json数组中

SELECT id,company_code,position_name,
JSON_EXTRACT(work_address,'$[*].cityName'),
JSON_EXTRACT(work_address,'$[0].cityName') AS work_address_city1,
JSON_EXTRACT(work_address,'$[1].cityName') AS work_address_city2,
work_address
FROM job_position WHERE '北京市' member of(JSON_EXTRACT(work_address,'$[*].cityName')); 

结果:

 关于mysql json解析的函数还有可以查看官方文档

比如

JSON_CONTAINS(json_doc, val[, path])  查询json是否包含某个元素

JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)  查询是否存在指定路径，存在则返回1，否则返回0。

 ......

 

mysql官方json介绍文档：https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html