MySql查询JSON字符串(包含多层级复杂JSON查询)

1.模糊查询json类型字段
存储的数据格式（字段名 people_json）：

{“name”: “zhangsan”, “age”: “13”, “gender”: “男”}

代码如下（示例）：

select * from table_name where people_json->'$.name' like '%zhang%'

2.精确查询json类型字段
存储的数据格式（字段名 people_json）：

{“name”: “zhangsan”, “age”: “13”, “gender”: “男”}

代码如下（示例）：

select * from table_name where people_json-> '$.age' = 13

3.模糊查询JsonArray类型字段
存储的数据格式（字段名 people_json）：

[{“name”: “zhangsan”, “age”: “13”, “gender”: “男”}]

代码如下（示例）：

select * from table_name where people_json->'$[*].name' like '%zhang%'

4.精确查询JsonArray类型字段
存储的数据格式（字段名 people_json）：

[{“name”: “zhangsan”, “age”: “13”, “gender”: “男”}]

代码如下（示例）：

select * from table_name where JSON_CONTAINS(people_json,JSON_OBJECT('age', "13"))

5多层级关系，模糊查询所有的

存储的数据格式（字段名 people_json）：

[{“a”: “zhangsan”, “b”: “13”, “person”: {“name”: “zhangsan”, “age”: “13”, “gender”: “男”}]}]

select * from table_name where people_json->'$**.name' like '%zhang%'

或者

多层级关系，精确匹配 查询

select * from table_name where JSON_SEARCH(people_json, 'all', 'zhang',NULL, '$**.name')  !=''

异常情况处理：

但是如果JSON中没有age键，那么上述查询将会报错。为了解决这个问题，我们可以使用MySQL的内置函数和运算符来处理。

MySQL提供了一些内置函数来处理JSON查询，我们可以利用这些函数来判断JSON是否含有某个键值，以避免报错。下面是一些常用的内置函数：

JSON_VALID: 用于检查给定的字符串是否为有效的JSON。
JSON_TYPE: 用于返回给定键的值的类型。
JSON_CONTAINS: 用于检查给定键是否存在于JSON字符串中。
JSON_EXTRACT: 用于提取JSON字符串中指定键的值。
 

6.使用JSON_VALID函数进行查询

SELECT * FROM table_name  WHERE JSON_VALID(people_json);

7.使用JSON_TYPE函数进行查询
JSON_TYPE函数用于返回给定键的值的类型。我们可以在查询中使用它来判断JSON中是否存在某个键，以避免报错。以下是一个示例查询，只返回含有age键的记录：

SELECT * FROM table_name  WHERE JSON_TYPE(people_json->'$.age') IS NOT NULL;

8.使用JSON_CONTAINS函数进行查询
JSON_CONTAINS函数用于检查给定键是否存在于JSON字符串中。我们可以在查询中使用它来判断JSON中是否存在某个键，以避免报错。以下是一个示例查询，只返回含有age键的记录：

SELECT * FROM table_name  WHERE JSON_CONTAINS(people_json, 'age', '$.');

9.使用JSON_EXTRACT函数进行查询
JSON_EXTRACT函数用于提取JSON字符串中指定键的值。我们可以在查询中使用它来提取某个键的值，并进行进一步的判断。以下是一个示例查询，只返回年龄大于等于25岁的记录：

SELECT * FROM table_name  WHERE JSON_EXTRACT(people_json, '$.age') >= 25;