MySQL Json 字段类型操作

标签：name -- json JSON Json MySQL 类型 path id

创建表


CREATE TABLE `demo`  (
  `id` varchar(36) PRIMARY KEY ,
  `username` varchar(100),
  `password` varchar(100),
  `nickname` varchar(100),
  `roles` json,
  `status` int
)

插入

json格式的字段，插入时会自动校验格式，如果格式不是json的，会报错

INSERT INTO `demo` VALUES ('833d52048c653ffd282b4d8bd44d6469', NULL, NULL, '李1', '[{\"id\": \"1\", \"code\": \"RoleCode11\", \"name\": \"普通用户11\"}, {\"id\": \"1\", \"code\": \"RoleCode12\", \"name\": \"普通用户12\"}, {\"id\": \"1\", \"code\": \"RoleCode13\", \"name\": \"普通用户13\"}]', 1);
INSERT INTO `demo` VALUES ('04bfdca6b2366206d590a508ab176ed8', NULL, NULL, '李2', '[{\"id\": \"2\", \"code\": \"RoleCode21\", \"name\": \"普通用户21\"}, {\"id\": \"2\", \"code\": \"RoleCode22\", \"name\": \"普通用户22\"}, {\"id\": \"2\", \"code\": \"RoleCode23\", \"name\": \"普通用户23\"}]', 1);
INSERT INTO `demo` VALUES ('25729744994a10b8986c4bab4baa9847', NULL, NULL, '李3', '[{\"id\": \"3\", \"code\": \"RoleCode31\", \"name\": \"普通用户31\"}, {\"id\": \"3\", \"code\": \"RoleCode32\", \"name\": \"普通用户32\"}, {\"id\": \"3\", \"code\": \"RoleCode33\", \"name\": \"普通用户33\"}]', 1);
INSERT INTO `demo` VALUES ('77d94515acb00db06c687bdae68196b5', NULL, NULL, '李4', '[{\"id\": \"4\", \"code\": \"RoleCode41\", \"name\": \"普通用户41\"}, {\"id\": \"4\", \"code\": \"RoleCode42\", \"name\": \"普通用户42\"}, {\"id\": \"4\", \"code\": \"RoleCode43\", \"name\": \"普通用户43\"}]', 1);
INSERT INTO `demo` VALUES ('b04e4a99d1892f2ad8b3ca5802e5e168', NULL, NULL, '李5', '[{\"id\": \"5\", \"code\": \"RoleCode51\", \"name\": \"普通用户51\"}, {\"id\": \"5\", \"code\": \"RoleCode52\", \"name\": \"普通用户52\"}, {\"id\": \"5\", \"code\": \"RoleCode53\", \"name\": \"普通用户53\"}]', 1);
INSERT INTO `demo` VALUES ('b04e4a99d1892f2ad8b3ca5802e5e166', NULL, NULL, '李6', '{\"id\": \"6\", \"code\": \"RoleCode61\", \"name\": \"普通用户61\"}', 1);
INSERT INTO `demo` VALUES ('b04e4a99d1892f2ad8b3ca5802e5e167', NULL, NULL, '可不带\线', '{"id": "7", "code": "RoleCode71", "name": "普通用户71"}', 1);

插入数据

# JSON_ARRAY()函数插入数组
insert into demo(id,nickname,roles) values('1','JSON_ARRAY',JSON_ARRAY(1, "JSON_ARRAY", null, true,curtime()));
#  JSON_OBJECT()函数插入对象
insert into demo(id,nickname,roles) values('2','JSON_OBJECT',json_object('id',11, 'name','JSON_OBJECT'));

JSON_OBJECT()函数插入对象
对于 JSON 文档，KEY 名不能重复。
如果插入的值中存在重复 KEY，在 MySQL 8.0.3 之前，遵循 first duplicate key wins 原则，会保留第一个 KEY，后面的将被丢弃掉。
从 MySQL 8.0.3 开始，遵循的是 last duplicate key wins 原则，只会保留最后一个 KEY。

查询

JSON_ARRAYAGG()和JSON_OBJECTAGG()将查询结果封装成json

# 将字段值，组装成 JSON ARRAYAGG 格式
SELECT id,nickname,JSON_ARRAYAGG(nickname) FROM demo  GROUP BY id
# 将字段值，组装成 JSON_OBJECTAGG 格式
SELECT id,nickname,JSON_OBJECTAGG(id,nickname)  FROM demo  GROUP BY id

CAST()将字符串转成json

转了以后就不带\转义字符了

set @j = '{"a": 1, "b": [2, 3], "a c": 4}';
-- {"a": "{\"qq\": 22, \"ww\": 33}", "b": [2, 3], "a c": 4}
select JSON_SET(@j, '$.a', '{"qq": 22, "ww": 33}');
-- {"a": {"qq": 22, "ww": 33}, "b": [2, 3], "a c": 4}
select JSON_SET(@j, '$.a', CAST('{"qq": 22, "ww": 33}' as JSON));

JSON类型的解析

JSON_EXTRACT()解析json

其中，JSON_doc 是 JSON 文档，path 是路径。该函数会从 JSON 文档提取指定路径（path）的元素。如果指定 path 不存在，会返回 NULL。可指定多个 path，匹配到的多个值会以数组形式返回。
非list

-- 解析数组
-- 取下标为1的数组值（数组下标从0开始），结果：20
SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]');
-- 取多个，结果返回是一个数组，结果：[20, 10]
SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]');
-- 可以使用*获取全部，结果：[30, 40]
SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]');

-- 还可通过 [M to N] 获取数组的子集
-- 结果：[10, 20]
select JSON_EXTRACT('[10, 20, [30, 40]]', '$[0 to 1]');
-- 这里的 last 代表最后一个元素的下标，结果：[20, [30, 40]]
select JSON_EXTRACT('[10, 20, [30, 40]]', '$[last-1 to last]');

-- 解析对象：对象的路径是通过 KEY 来表示的。
set @j='{"a": 1, "b": [2, 3], "a c": 4}';

-- 如果 KEY 在路径表达式中不合法（譬如存在空格），则在引用这个 KEY 时，需用双引号括起来。
-- 结果： 1 4 3
select json_extract(@j, '$.a'), json_extract(@j, '$."a c"'), json_extract(@j, '$.b[1]');
-- 使用*获取所有元素，结果：[1, [2, 3], 4]
select json_extract('{"a": 1, "b": [2, 3], "a c": 4}', '$.*');
-- 这里的 $**.b 匹配 $.a.b 和 $.c.b，结果：[1, 2]
select json_extract('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b');

json_extract解析出来的数据，可以灵活用于where、order by等等所有地方。

-> 箭头函数解析json

column->path，包括后面讲到的 column->>path，都是语法糖，在实际使用的时候都会在底层自动转化为 JSON_EXTRACT。
column->path 等同于 JSON_EXTRACT(column, path) ，只能指定一个path。

# roles -> '$.name' 会自动解析成 JSON_EXTRACT(roles,'$.name') 
select roles,JSON_EXTRACT(roles,'$.name'), roles -> '$.name' from demo;

JSON_QUOTE()引用与JSON_UNQUOTE()取消引用

JSON_QUOTE(string)，生成有效的 JSON 字符串，主要是对一些特殊字符（如双引号）进行转义。

-- 结果："null"	"\"null\""	"[1, 2, 3]"
select json_quote('null'), json_quote('"null"'), json_quote('[1, 2, 3]');

JSON_UNQUOTE(json_val)，将 JSON 转义成字符串输出。常用于使用JSON_EXTRACT()和->函数解析完之后，去除引号。

JSON_UNQUOTE()特殊字符转义表：
转义序列由序列表示的字符

转义序列	由序列表示的字符
\"	双引号
\b	退格字符
\f	换页字符
\n	换行符
\r	回车符
\t	制表符
\\	反斜杠()字符
\uXXXX	Unicode XXXX 转UTF-8

-- 字符串类型转换后会去掉引号，结果："jack"	jack	1	0
-- 数字类型不会有额外效果
select roles, roles->'$.name',json_unquote(roles->'$.name'),json_valid(roles->'$.name'),json_valid(json_unquote(roles->'$.name')) from demo;

直观地看，没加 JSON_UNQUOTE 字符串会用双引号引起来，加了 JSON_UNQUOTE 就没有。但本质上，前者是 JSON 中的 STRING 类型，后者是 MySQL 中的字符类型，这一点可通过 JSON_VALID 来判断。

->>箭头解析json

同 column->path 类似，只不过其返回的是字符串，相当于将字符串的双引号去掉了，是一个语法糖，本质上是执行了JSON_UNQUOTE( JSON_EXTRACT(column, path) )。

以下三者是等价的：

JSON_UNQUOTE( JSON_EXTRACT(column, path) )
JSON_UNQUOTE(column -> path)
column->>path

select roles, roles->'$.name',json_unquote(roles->'$.name'),roles->>'$.name', JSON_UNQUOTE( JSON_EXTRACT(roles, '$.name')) from demo;

JSON类型的查询

JSON_CONTAINS()判断是否包含

判断 target 文档是否包含 candidate 文档，包含的话返回1，不包含的话返回0
如果带了path，就判断path中的数据是否等于candidate，等于的话返回1，不等于的话返回0
函数前加not可取反

SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
SET @j2 = '{"a":1}';
-- 判断@j中是否包含@j2，结果：1
SELECT JSON_CONTAINS(@j, @j2);

SET @j2 = '1';
-- 判断@j字段中的a是否等于1，结果：1
SELECT JSON_CONTAINS(@j, @j2, '$.a');
-- 结果：0
SELECT JSON_CONTAINS(@j, @j2, '$.b');

SET @j2 = '{"d": 4}';
-- 结果：0
SELECT JSON_CONTAINS(@j, @j2, '$.a');
-- 结果：1
SELECT JSON_CONTAINS(@j, @j2, '$.c');

SET @j = '[1, "a", 1.02]';
SET @j2 = '"a"';
-- 判断@j数组中是否包含@j2，结果：1
SELECT JSON_CONTAINS(@j, @j2)；

JSON_CONTAINS_PATH()判断

格式：JSON_CONTAINS_PATH(JSON_doc, one_or_all, path[, path] …)
判断指定的 path 是否存在，存在，则返回 1，否则是 0。
函数中的 one_or_all 可指定 one 或 all，one 是任意一个路径存在就返回 1，all 是所有路径都存在才返回 1。

SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
-- a或者e 存在一个就返回1，结果：1
SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e');
-- a和e都存在返回1，结果：0
SELECT JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e');
-- c中的d存在返回1，结果：1
SELECT JSON_CONTAINS_PATH(@j, 'one', '$.c.d');

SET @j = '[1, 4, "a", "c"]';
-- @j是一个数组，$[1]判断第二个数据是否存在，结果为1
select JSON_CONTAINS_PATH(@j, 'one', '$[1]');
-- $[11]判断第11个数据不存在，结果为0
select JSON_CONTAINS_PATH(@j, 'one', '$[11]');

标签：name,--,json,JSON,Json,MySQL,类型,path,id
From： https://www.cnblogs.com/vipsoft/p/18525621

创建表

插入