009：JSON

--这个 employee 对象是包含 3 个员工记录（对象）的数组。{"employees": [{ "firstName":"John" , "lastName":"Doe" }, { "firstName":"Anna" , "lastName":"Smith" }, { "firstName":"Peter" , "lastName":"Jones" }]}

---- SQL创建User表--create table user ( ???id bigint not null auto_increment, ???user_name varchar(10), ???age int, ???primary key(id));

## JSON定义的User表#db.user.insert({ ???user_name:"tom", ???age:30})db.createCollection("user")

---- 创建带json字段的表--CREATE TABLE t_user ( ???uid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, ???name VARCHAR(32) NOT NULL, ???email VARCHAR(128) NOT NULL, ???address VARCHAR(256) NOT NULL, ???UNIQUE KEY (name), ???UNIQUE KEY (email) )charset=utf8mb4;mysql> INSERT INTO t_user VALUES (NULL,‘David‘,‘david@gmail‘,‘Shanghai ...‘);INSERT INTO t_user VALUES (NULL,‘Amy‘,‘amy@gmail‘,‘Beijing ...‘);INSERT INTO t_user VALUES (NULL,‘Tom‘,‘tom@gmail‘,‘Guangzhou ...‘);Query OK, 1 row affectedQuery OK, 1 row affectedQuery OK, 1 row affectedmysql> select * from t_user;+-----+-------+-------------+---------------+| uid | name ?| email ??????| address ??????|+-----+-------+-------------+---------------+| ??1 | David | david@gmail | Shanghai ... ?|| ??2 | Amy ??| amy@gmail ??| Beijing ... ??|| ??3 | Tom ??| tom@gmail ??| Guangzhou ... |+-----+-------+-------------+---------------+3 rows in setmysql> DROP TABLE IF EXISTS t_user_json; ???CREATE TABLE t_user_json( ???uid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, ???data JSON);-- JSON_OBJECT将对象列表转化成JSON对象(key唯一,必须是偶数 key - value key - value)mysql> insert into t_user_json SELECT uid,JSON_OBJECT(‘name‘,name,‘email‘,email,‘address‘,address) AS dataFROM t_user;Query OK, 3 rows affectedRecords: 3 ?Duplicates: 0 ?Warnings: 0mysql> select * from t_user_json;+-----+----------------------------------------------------------------------+| uid | data ????????????????????????????????????????????????????????????????|+-----+----------------------------------------------------------------------+| ??1 | {"name": "David", "email": "david@gmail", "address": "Shanghai ..."} || ??2 | {"name": "Amy", "email": "amy@gmail", "address": "Beijing ..."} ?????|| ??3 | {"name": "Tom", "email": "tom@gmail", "address": "Guangzhou ..."} ???|+-----+----------------------------------------------------------------------+3 rows in set--JSON_EXTRACT() 是JSON提取函数，$.address 就是一个 JSON path，表示定位文档的 address 字段JSON path mysql> SELECT uid,JSON_EXTRACT(data,‘$.address‘) from t_user_json;+-----+--------------------------------+| uid | JSON_EXTRACT(data,‘$.address‘) |+-----+--------------------------------+| ??1 | "Shanghai ..." ????????????????|| ??2 | "Beijing ..." ?????????????????|| ??3 | "Guangzhou ..." ???????????????|+-----+--------------------------------+3 rows in setmysql> SELECT uid,JSON_EXTRACT(data,‘$.address2‘) from t_user_json;+-----+---------------------------------+| uid | JSON_EXTRACT(data,‘$.address2‘) |+-----+---------------------------------+| ??1 | NULL ???????????????????????????|| ??2 | NULL ???????????????????????????|| ??3 | NULL ???????????????????????????|+-----+---------------------------------+3 rows in set--JSON_INSERT() 是JSON 插入函数mysql> ?UPDATE t_user_json set data = json_insert(data,"$.address2","HangZhou ...") where uid = 1;Query OK, 1 row affectedRows matched: 1 ?Changed: 1 ?Warnings: 0mysql> SELECT uid,JSON_EXTRACT(data,‘$.address1‘) from t_user_json;+-----+---------------------------------+| uid | JSON_EXTRACT(data,‘$.address1‘) |+-----+---------------------------------+| ??1 | NULL ???????????????????????????|| ??2 | NULL ???????????????????????????|| ??3 | NULL ???????????????????????????|+-----+---------------------------------+3 rows in setmysql> SELECT * from t_user_json;+-----+--------------------------------------------------------------------------------------------------+| uid | data ????????????????????????????????????????????????????????????????????????????????????????????|+-----+--------------------------------------------------------------------------------------------------+| ??1 | {"name": "David", "email": "david@gmail", "address": "Shanghai ...", "address2": "HangZhou ..."} || ??2 | {"name": "Amy", "email": "amy@gmail", "address": "Beijing ..."} ?????????????????????????????????|| ??3 | {"name": "Tom", "email": "tom@gmail", "address": "Guangzhou ..."} ???????????????????????????????|+-----+--------------------------------------------------------------------------------------------------+3 rows in set-- JSON_MERGE将两个或以上的JSON对象融合 mysql> select json_merge(JSON_EXTRACT(data,‘$.address‘) ,JSON_EXTRACT(data,‘$.address2‘)) from t_user_json;+-----------------------------------------------------------------------------+| json_merge(JSON_EXTRACT(data,‘$.address‘) ,JSON_EXTRACT(data,‘$.address2‘)) |+-----------------------------------------------------------------------------+| ["Shanghai ...", "HangZhou ..."] ???????????????????????????????????????????|| NULL ???????????????????????????????????????????????????????????????????????|| NULL ???????????????????????????????????????????????????????????????????????|+-----------------------------------------------------------------------------+3 rows in setmysql> begin;UPDATE t_user_json set data = json_array_append(data,"$.address",JSON_EXTRACT(data,‘$.address2‘))where JSON_EXTRACT(data,‘$.address2‘) IS NOT NULL AND uid >0;select JSON_EXTRACT(data,‘$.address‘) from t_user_json;UPDATE t_user_json set data = JSON_REMOVE(data,‘$.address2‘) where uid>0;commit;Query OK, 0 rows affectedQuery OK, 1 row affectedRows matched: 1 ?Changed: 1 ?Warnings: 0+----------------------------------+| JSON_EXTRACT(data,‘$.address‘) ??|+----------------------------------+| ["Shanghai ...", "HangZhou ..."] || "Beijing ..." ???????????????????|| "Guangzhou ..." ?????????????????|+----------------------------------+3 rows in setQuery OK, 1 row affectedRows matched: 3 ?Changed: 1 ?Warnings: 0Query OK, 0 rows affected

create table demo(id int unsigned primary key auto_increment,comment json);insert into demo(id,name) values(1,‘{"programmers": [{"email": "aaaa", "lastName": "McLaughlin", "firstName": "Brett"}, {"email": "bbbb", "lastName": "Hunter", "firstName": "Jason"}]}‘);-- 检查第一层的key值 json_keys-- select json_keys(comment) from demo-- 从JSON中提取 json_extract-- select json_extract(comment,‘$.programmers[0].email‘) from demo -- 从Json中去除元素 json_remove-- select json_extract(comment,‘$.programmers‘),json_remove(comment,‘$.programmers[0]‘) from demo-- Json是否包含当前路径 json_contains_path-- select json_contains_path(comment,"all","$.programmers[0].firstName") from demo -- 判断JSON当前路径对象类型 JSON_TYPE-- select JSON_TYPE(comment),JSON_TYPE(comment->"$.programmers[0].firstName"),JSON_TYPE(comment->"$.programmers")="ARRAY" from demo-- 创建数组对象 JSON_ARRAY-- SELECT JSON_ARRAY(‘a‘, 1, RAND());-- 将对象列表转化成JSON对象(key唯一,必须是偶数 key - value key - value) JSON_OBJECT-- SELECT JSON_OBJECT(‘key1‘, 1, ‘key2‘, ‘abc‘),JSON_OBJECT(‘key1‘, 1, ‘key2‘, ‘abc‘, ‘key1‘, ‘def‘); -- 将两个或以上的JSON对象融合 JSON_MERGE-- SELECT JSON_MERGE(‘["a", 1]‘, ‘{"key": "value"}‘),JSON_MERGE(‘{"a": 2,"b":"2"}‘,‘{"key": "value"}‘),JSON_MERGE(‘{"a": 1, "b": 2}‘, ‘{"c": 3, "a": 4}‘);-- 用户定义的变量不能JSON数据类型 函数 COLLATION(返回字符串参数的排序方式)-- SET @j = JSON_OBJECT(‘key‘, ‘value‘); SELECT CHARSET(@j), COLLATION(@j);-- 因为utf8mb4_bin是二进制排序规则，JSON值比较是区分大小写的。-- SELECT JSON_ARRAY(‘x‘) = JSON_ARRAY(‘X‘);-- JSON对大小写敏感,SQL非空不敏感-- SELECT JSON_VALID(‘null‘), JSON_VALID(‘Null‘), JSON_VALID(‘NULL‘),ISNULL(null), ISNULL(Null), ISNULL(NULL);-- 将JSON中元素替换;如果位置不存在，则追加 JSON_SET-- SET @j = ‘["a", {"b": [true, false]}, [10, 20]]‘;SELECT JSON_SET(@j, ‘$[1].b[0]‘, 1, ‘$[2][3]‘, 3);-- 向JSON中添加元素,原来位置存在数据不会替换 JSON_INSERT-- SELECT JSON_INSERT(@j, ‘$[1].b[0]‘, 1, ‘$[2][2]‘, 2);-- 替换JSON中原有值,不存在的不会替换 JSON_REPLACE-- SELECT JSON_REPLACE(@j, ‘$[1].b[0]‘, JSON_OBJECT(‘key‘, ‘value‘), ‘$[2][2]‘, 2);-- 移除JSON元素列表 JSON_REMOVE-- SELECT JSON_REMOVE(@j, ‘$[2]‘, ‘$[1].b[1]‘, ‘$[1].b[1]‘);-- 判断JSON中是否包含‘值‘,在这个路径下 JSON_CONTAINS-- SELECT JSON_CONTAINS(‘{"a": 1, "b": 2, "c": {"d": 4}}‘, ‘1‘, ‘$.a‘);-- 去掉JSON格式""号 json_unquote , ->> 这两个是等价的-- select json_unquote(comment->"$.programmers[1].email"), comment->>"$.programmers[1].email"from demo---- json_remove 从json记录中删除数据-- 原型 : JSON_REMOVE(json_doc, path[, path] ...)--mysql> set @j = ‘["a", ["b", "c"], "d"]‘; ??Query OK, 0 rows affected (0.00 sec)mysql> select json_remove(@j, ‘$[1]‘);+-------------------------+| json_remove(@j, ‘$[1]‘) |+-------------------------+| ["a", "d"] ?????????????| ?-- 删除了下标为1的元素["b", "c"]+-------------------------+1 row in set (0.00 sec)mysql> update user set data = json_remove(data, "$.address_2") where uid = 1;Query OK, 1 row affected (0.03 sec)Rows matched: 1 ?Changed: 1 ?Warnings: 0mysql> select * from user;+-----+------------------------------------------------------+| uid | data ????????????????????????????????????????????????|+-----+------------------------------------------------------+| ??1 | {"age": 18, "name": "tom", "address": ["SZ", "BJ"]} ?| ?-- address_2 的字段删除了| ??2 | {"age": 28, "mail": "jim@163.com", "name": "jim"} ???|| ??4 | {"age": 33, "name": "jery", "email": "jery@163.com"} |+-----+------------------------------------------------------+3 rows in set (0.00 sec)

mysql> create table test_inex_1( ???-> data json, ???-> gen_col varchar(10) generated always as (json_extract(data, ‘$.name‘)), ?-- 抽取data中的name， 生成新的一列，名字为gen_col ???-> index idx (gen_col) ?-- 将gen_col 作为索引 ???-> );Query OK, 0 rows affected (0.13 sec)mysql> show create table test_index_1；-- -----省略表格线-----| test_index_1 | CREATE TABLE `test_index_1` ( ?`data` json DEFAULT NULL, ?`gen_col` varchar(10) GENERATED ALWAYS AS (json_extract(data, ‘$.name‘)) VIRTUAL, ?KEY `idx` (`gen_col`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |-- -----省略表格线-----1 row in set (0.00 sec)mysql> insert into test_index_1(data) values (‘{"name":"tom", "age":18, "address":"SH"}‘);Query OK, 1 row affected (0.04 sec)mysql> insert into test_index_1(data) values (‘{"name":"jim", "age":28, "address":"SZ"}‘); ?????Query OK, 1 row affected (0.03 sec)mysql> select * from test_index_1;+---------------------------------------------+---------+| data ???????????????????????????????????????| gen_col |+---------------------------------------------+---------+| {"age": 18, "name": "tom", "address": "SH"} | "tom" ??|| {"age": 28, "name": "jim", "address": "SZ"} | "jim" ??|+---------------------------------------------+---------+2 rows in set (0.00 sec)mysql> select json_extract(data,"$.name") as username from test_index_1 where gen_col="tom"; ?-- 如果这样做，为空，原因如下Empty set (0.00 sec)mysql> select hex(‘"‘);+----------+| hex(‘"‘) |+----------+| 22 ??????| ?-- 双引号的 16进制+----------+1 row in set (0.00 sec)mysql> select hex(gen_col) from test_index_1;+--------------+| hex(gen_col) |+--------------+| 226A696D22 ??| ?-- 双引号本身也作为了存储内容| 22746F6D22 ??|+--------------+2 rows in set (0.00 sec)mysql> select json_extract(data,"$.name") as username from test_index_1 where gen_col=‘"tom"‘; ?-- 使用‘"tome"‘,用单引号括起来+----------+| username |+----------+| "tom" ???| ?-- 找到了对应的数据+----------+1 row in set (0.00 sec)mysql> explain select json_extract(data,"$.name") as username from test_index_1 where gen_col=‘"tom"‘\G*************************** 1. row *************************** ??????????id: 1 ?select_type: SIMPLE ???????table: test_index_1 ??partitions: NULL ????????type: ref possible_keys: idx ???-- 使用了 key idx ?????????key: idx ?????key_len: 43 ?????????ref: const ????????rows: 1 ????filtered: 100.00 ???????Extra: NULL1 row in set, 1 warning (0.00 sec)------ 建立表的时候去掉双引用---mysql> create table test_index_2 ( ???-> data json, ???-> gen_col varchar(10) generated always as ( ???-> ???json_unquote( ???-- 使用json_unquote函数进行去掉双引号 ???-> ????????????json_extract(data, "$.name") ???-> ???)), ???-> key idx(gen_col) ???-> );Query OK, 0 rows affected (0.13 sec)mysql> show create table test_index_2;-- -----省略表格线-----| test_index_2 | CREATE TABLE `test_index_2` ( ?`data` json DEFAULT NULL, ?`gen_col` varchar(10) GENERATED ALWAYS AS (json_unquote( ???????????json_extract(data, "$.name") ??)) VIRTUAL, ?KEY `idx` (`gen_col`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |-- -----省略表格线-----1 row in set (0.00 sec)mysql> insert into test_index_2(data) values (‘{"name":"tom", "age":18, "address":"SH"}‘);Query OK, 1 row affected (0.03 sec)mysql> insert into test_index_2(data) values (‘{"name":"jim", "age":28, "address":"SZ"}‘);Query OK, 1 row affected (0.02 sec)mysql> select json_extract(data,"$.name") as username from test_index_2 where gen_col="tom"; ?-- 未加单引号+----------+| username |+----------+| "tom" ???| ?-- 可以找到数据+----------+1 row in set (0.00 sec)mysql> explain select json_extract(data,"$.name") as username from test_index_2 where gen_col="tom"\G*************************** 1. row *************************** ??????????id: 1 ?select_type: SIMPLE ???????table: test_index_2 ??partitions: NULL ????????type: refpossible_keys: idx ??-- 使用了 key idx ?????????key: idx ?????key_len: 43 ?????????ref: const ????????rows: 1 ????filtered: 100.00 ???????Extra: NULL1 row in set, 1 warning (0.00 sec)

---- 使用之前的user表操作--mysql> show create table user;-- -----省略表格线-----| user ?| CREATE TABLE `user` ( ?`uid` int(11) NOT NULL AUTO_INCREMENT, ?`data` json DEFAULT NULL, ?PRIMARY KEY (`uid`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 |-- -----省略表格线-----1 row in set (0.00 sec)mysql> select * from user;+-----+------------------------------------------------------+| uid | data ????????????????????????????????????????????????|+-----+------------------------------------------------------+| ??1 | {"age": 18, "name": "tom", "address": ["SZ", "BJ"]} ?|| ??2 | {"age": 28, "mail": "jim@163.com", "name": "jim"} ???|| ??4 | {"age": 33, "name": "jery", "email": "jery@163.com"} |+-----+------------------------------------------------------+mysql> alter table user ????-> add user_name varchar(32) ???-> generated always as (json_extract(data,"$.name")) virtual;Query OK, 0 rows affected (0.05 sec)Records: 0 ?Duplicates: 0 ?Warnings: 0-- virtual 关键字是不将该列的字段值存储，对应的是storedmysql> select user_name from user; ?+-----------+| user_name |+-----------+| "tom" ????|| "jim" ????|| "jery" ???|+-----------+3 rows in set (0.00 sec)mysql> alter table user add index idx(user_name); ?????????Query OK, 0 rows affected (0.13 sec)Records: 0 ?Duplicates: 0 ?Warnings: 0mysql> select * from user where user_name=‘"tom"‘; ?-- 加单引号+-----+-----------------------------------------------------+-----------+| uid | data ???????????????????????????????????????????????| user_name |+-----+-----------------------------------------------------+-----------+| ??1 | {"age": 18, "name": "tom", "address": ["SZ", "BJ"]} | "tom" ????|+-----+-----------------------------------------------------+-----------+1 row in set (0.00 sec)mysql> explain select * from user where user_name=‘"tom"‘\G*************************** 1. row *************************** ??????????id: 1 ?select_type: SIMPLE ???????table: user ??partitions: NULL ????????type: refpossible_keys: idx ??-- 使用了 key idx ?????????key: idx ?????key_len: 131 ?????????ref: const ????????rows: 1 ????filtered: 100.00 ???????Extra: NULL1 row in set, 1 warning (0.00 sec)mysql> show create table user;-- -----省略表格线-----| user ?| CREATE TABLE `user` ( ?`uid` int(11) NOT NULL AUTO_INCREMENT, ?`data` json DEFAULT NULL, ?`user_name` varchar(32) GENERATED ALWAYS AS (json_extract(data,"$.name")) VIRTUAL, ?`user_name2` varchar(32) GENERATED ALWAYS AS (json_extract(data,"$.name")) VIRTUAL, ?PRIMARY KEY (`uid`), ?KEY `idx` (`user_name`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 |-- -----省略表格线-----1 row in set (0.00 sec)

---- 姜boss演示JSON的SQL--drop table if exists User;CREATE TABLE User ( ???uid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, ???name VARCHAR(32) NOT NULL, ???email VARCHAR(256) NOT NULL, ???address VARCHAR(512) NOT NULL, ???UNIQUE KEY (name), ???UNIQUE KEY (email));INSERT INTO User VALUES (NULL,‘David‘,‘david@gmail‘,‘Shanghai ...‘);INSERT INTO User VALUES (NULL,‘Amy‘,‘amy@gmail‘,‘Beijing ...‘);INSERT INTO User VALUES (NULL,‘Tom‘,‘tom@gmail‘,‘Guangzhou ...‘);SELECT * FROM User;ALTER TABLE User ADD COLUMN address2 VARCHAR(512) NOT NULL;ALTER TABLE User ADD COLUMN passport VARCHAR(64) NOT NULL;DROP TABLE IF EXISTS UserJson;CREATE TABLE UserJson( ???uid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, ???data JSON);truncate table UserJson;insert into UserJson SELECT ????uid,JSON_OBJECT(‘name‘,name,‘email‘,email,‘address‘,address) AS dataFROM ???User; ???SELECT * FROM UserJson; SELECT uid,JSON_EXTRACT(data,‘$.address2‘) from UserJson; ???UPDATE UserJsonset data = json_insert(data,"$.address2","HangZhou ...")where uid = 1;SELECT JSON_EXTRACT(data,‘$.address[1]‘) from UserJson;select json_merge(JSON_EXTRACT(data,‘$.address‘) ,JSON_EXTRACT(data,‘$.address2‘)) from UserJson;begin;UPDATE UserJsonset data = json_array_append(data,"$.address",JSON_EXTRACT(data,‘$.address2‘))where JSON_EXTRACT(data,‘$.address2‘) IS NOT NULL AND uid >0;select JSON_EXTRACT(data,‘$.address‘) from UserJson;UPDATE UserJsonset data = JSON_REMOVE(data,‘$.address2‘)where uid>0;commit;