mysql5.7 json相关

1. 建表：

   CREATE TABLE `tb_user` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `content` json DEFAULT NULL,
     `content2` json DEFAULT NULL,
     `name` varchar(255) COLLATE utf8_bin DEFAULT NULL,
     PRIMARY KEY (`id`)
   ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
   

INSERT into tb_user (content,content2) values(JSON_OBJECT("id", 3, "name",  "vbsj"),JSON_ARRAY(1,2,3))

3. INSERT INTO `lnmp` (category, tags) VALUES ('{"id": 1, "name": "lnmp.cn"}', '[1, 2, 3]')
   

就是插入 json 格式的字符串，可以是对象的形式，也可以是数组的形式

MySQL 也有专门的函数 JSON_OBJECT，JSON_ARRAY 生成 json 格式的数据

查询 json 中的数据用 *column->path 的形式，其中对象类型 path 这样表示 .path∗,而数组类型则是 ∗.path, 而数组类型则是 *.path∗,而数组类型则是 ∗[index]

eg1：category->’$.name’：取json对象里的name属性，结果集上会有双引号

在5.7.13后添加了category->>$.name’:可以得到无引号的属性值

同时category->>$.nam{e}^{\prime }等价于JSO{N}_{U}NQUOTE（category-{>}^{\prime }$.name’）

eg2：对于json数组类型使用content2->’$[2]’，取下标为2的值

json条件判断相等条件相等时，不能采取字符串相等的形式来判断，无法获取对应值。可以使用JSON_OBJECT，JSON_ARRAY把string转成相应对象，也可以通过CAST 将字符串转成 JSON 的形式。

-- 查询json 
SELECT id,content->'$.id' cid ,content->'$.name' cname,content2->'$[2]' c FROM tb_user

SELECT id,content->'$.id' cid ,content->>'$.name' cname,content2->'$[2]' c FROM tb_user
-- json条件查询
字符串的json查询结果为null
SELECT * FROM tb_user where content2='[5, 6, 7, 8]'
将string转json
SELECT * FROM tb_user where content=JSON_OBJECT("id", 2, "name", "php.net")
SELECT * FROM tb_user where content2=CAST('[5, 6, 7, 8]' as json)

-- 通过 JSON 中的元素进行查询, 对象型的查询同样可以通过 column->path
->和->>在string元素查询时没有区分
SELECT * from tb_user where content->'$.name' ='vbsj'
SELECT * from tb_user where content->>'$.name' ='vbsj'
在json元素里，类型是严格区分的
SELECT * FROM tb_user where content2->'$[2]'=3          
SELECT * FROM tb_user where content2->'$[2]'='3'       》》》》》》》》》》》》null
--可以用JSON_CONTAINS 函数，但是此函数第二个参数不接受整数，否则会报错：Invalid data type for JSON data in argument 2 to function json_contains

SELECT * from tb_user where JSON_CONTAINS(content,'"vbsj"','$.name'); 》》》》》》》查string
SELECT * from tb_user where JSON_CONTAINS(content,'1','$.id');  》》》》》》》查integer

-- 更新 JSON
-- 整体更新，set时可以不用使用json_object，json_array
update tb_user SET content2='[2.3,2,6,5]' where id =1；
-- 元素更新 JSON_INSERT() 插入新值，但不会覆盖已经存在的值
UPDATE tb_user set content=JSON_INSERT(content,'$.name','xujing','$.url' ,'www.baidu.com') 
where id =1；》》》》》对于不存在的属性，走插入。而已存在的属性值不会发生改变。对于数组对象，下标没有的会在之前的补上，有的不会发生改变
-- JSON_SET() 插入新值，并覆盖已经存在的值
UPDATE tb_user set content2=JSON_SET(content2,'$[1]',5.5) where id =1
-- JSON_REPLACE() 只替换存在的值，不存在的忽略
UPDATE tb_user set content2=JSON_REPLACE(content2,'$[2]',44) where id =1
-- JSON_REMOVE（） 删除对应的属性，如果有参数为null，则返回null
 UPDATE tb_user set content2= JSON_REMOVE(content2, '$[1]')  where id=1

-- 虚拟列
alter table tb_user ADD user_name VARCHAR(20) generated always as (content->'$.name');
-- 添加普通索引
ALTER table tb_user add index idx_vname(user_name);

SELECT JSON_EXTRACT(content,'$.name') FROM tb_user；》》》》和*column->path 等价

》》》》匹配这个字段里有没有这两个当中其中一个属性，有则返回1，都没有返回0
SELECT JSON_CONTAINS_PATH(content,'one','$.name','$.pwd') from tb_user；
》》》》只有这个json字段里这两个属性都有才会返回1
SELECT JSON_CONTAINS_PATH(content,'all','$.name','$.pwd') from tb_user
》》》》查询某个json字段里的key值有哪些
 SELECT JSON_KEYS(content) from tb_user

https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html
JSON_ARRAYAGG()
JSON_OBJECTAGG()
JSON_PRETTY()
JSON_MERGE_PATCH()
JSON_MERGE_PRESERVE()