Mysql的JSON格式字段实用操作函数JSON_CONTAINS、JSON_SEARCH、JSON_EXTRACT

文章目录

• 前言
• 一、示例数据
• 二、使用
• • 1.JSON_CONTAINS
  • 2.JSON_SEARCH
  • 3.JSON_EXTRACT
• 总结

前言

在开发中难免会遇见在 Mysql 字段存储 JSON 格式数据的业务情况，记录几种常用函数的
用法。

一、示例数据

建一张表，字段 memo 存储 JSON 格式数据

CREATE TABLE `user` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` bigint DEFAULT NULL COMMENT '姓名',
  `age` bigint DEFAULT NULL COMMENT '年龄',
  `sex` bigint DEFAULT NULL COMMENT '性别',
  `memo` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '说明',
  PRIMARY KEY (`id`)
) 

memo 的数据为：

{
	"location":"位置 1",
	"purchaseTime":"2022-07-04",
	"assetsTypeName":"CPU",
	"useDepartment":"测试部",
	"strId":11
}

二、使用

1.JSON_CONTAINS

JSON_CONTAINS 函数用于检查一个 JSON 文档是否包含另一个 JSON 文档或值。它返回 1 表示包含，返回 0 表示不包含，返回 NULL 表示参数有误或不兼容。

示例：查询符合条件的 user 数据

SELECT * from user
WHERE memo IS NOT NULL 
and JSON_CONTAINS(memo, '"位置 1"', '$.location') 
and JSON_CONTAINS(memo, '"测试部"', '$.useDepartment');

SELECT * from user
WHERE memo IS NOT NULL 
and JSON_CONTAINS(memo, '11', '$.strId');

$.location：数据路径。
“位置 1”：具体值，注意这里匹配具体值的时候字符值要加双引号（“”），和上面 JSON 值对上，数字类型正常匹配就行。

2.JSON_SEARCH

JSON_SEARCH 函数用于在 JSON 文档中搜索指定值，返回该值的路径或位置。有两种模式：“one” 和 ”all”，分别返回第一个匹配的位置或所有匹配的位置。

示例：查询符合条件的 user 数据

SELECT * FROM user
WHERE memo IS NOT NULL 
and JSON_SEARCH(memo, 'one', '位置 1');


SELECT * FROM user
WHERE memo IS NOT NULL 
and JSON_SEARCH(memo, 'all', '位置 1');

3.JSON_EXTRACT

JSON_EXTRACT 函数用于从 JSON 文档中提取一个或多个值。

示例：查询符合条件的 useDepartment

SELECT 
    DISTINCT TRIM(BOTH '"' FROM JSON_EXTRACT(memo, '$.useDepartment')) AS useDepartment
FROM user
WHERE memoIS NOT NULL
AND JSON_EXTRACT(memo, '$.purchaseTime') ='2022-07-03'
AND JSON_EXTRACT(memo, '$.assetsTypeName') = 'CPU'
AND JSON_EXTRACT(memo, '$.location') = '位置 1'



SELECT 
    DISTINCT TRIM(BOTH '"' FROM JSON_EXTRACT(memo, '$.useDepartment')) AS useDepartment
FROM user
WHERE memoIS NOT NULL
AND memo->'$.purchaseTime' ='2022-07-03'
AND memo->'$.assetsTypeName' = 'CPU'
AND memo->'$.location' = '位置 1'

$.location：数据路径。
TRIM(BOTH‘”’FROM …)：移除字符串两边的双引号。

补充
TRIM() 函数：
从字符串中删除不必要的前导和后缀字符
用法：
TRIM([{BOTH|LEADING|TRAILING} [removed_str]] FROM str)

总结

mysql 针对 JSON 数据处理的函数还有很多，有兴趣的小伙伴可以自行了解学习。
Mysq 官网