Mariadb 10.2 JSON，mariadb10.2json

和通数据库htsjk.Com2019-07-17 01:12 来源:未知阅读:18279 评论 246 热度5

标签：

Mariadb 10.2 JSON，mariadb10.2json

Mariadb 10.2 JSON

Create Table

CREATE TABLE `codis3` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `info` varchar(4096) DEFAULT NULL,
  `person_in_charge` varchar(5) DEFAULT NULL,
  `phone` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `CONSTRAINT_1` CHECK (json_valid(`info`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Insert JSON data

insert into codis3 (info,person_in_charge,phone) value ('{"product_name":"cache27","fe_url":"192.168.1.124:9090/#cache27","proxy_info":[{"proxy_addr":"192.168.1.161:19000","admin_addr":"192.168.1.161:21000"},{"proxy_addr":"192.168.1.162:19000","admin_addr":"192.168.1.162:21000"}],"dashboard_info":{"admin_addr":"192.168.1.124:18080","zookeeper_addr":"192.168.1.124:2181,192.168.1.125:2181,192.168.1.127:2181"},"group_info":[{"group_id":1,"server":["192.168.1.161:6360","192.168.1.30:6360"]},{"group_id":2,"server":["192.168.1.161:6361","192.168.1.30:6361"]},{"group_id":3,"server":["192.168.1.161:6362","192.168.1.30:6362"]},{"group_id":4,"server":["192.168.1.161:6363","192.168.1.30:6364"]},{"group_id":5,"server":["192.168.1.161:6364","192.168.1.30:6364"]},{"group_id":6,"server":["192.168.1.162:6365","192.168.1.31:6365"]},{"group_id":7,"server":["192.168.1.162:6366","192.168.1.31:6366"]},{"group_id":8,"server":["192.168.1.162:6367","192.168.1.31:6367"]},{"group_id":9,"server":["192.168.1.162:6368","192.168.1.31:6368"]},{"group_id":10,"server":["192.168.1.162:6369","192.168.1.31:6369"]}]}', '某某', 123456789012);

Add virtual column

alter table codis3 add column product_name varchar(10) as (JSON_VALUE(info, '$.product_name')) VIRTUAL;

select * from codis3\G
*************************** 1. row ***************************
              id: 1
            info: {"product_name":"cache27","fe_url":"192.168.1.124:9090/#cache27","proxy_info":[{"proxy_addr":"192.168.1.161:19000","admin_addr":"192.168.1.161:21000"},{"proxy_addr":"192.168.1.162:19000","admin_addr":"192.168.1.162:21000"}],"dashboard_info":{"admin_addr":"192.168.1.124:18080","zookeeper_addr":"192.168.1.124:2181,192.168.1.125:2181,192.168.1.127:2181"},"group_info":[{"group_id":1,"server":["192.168.1.161:6360","192.168.1.30:6360"]},{"group_id":2,"server":["192.168.1.161:6361","192.168.1.30:6361"]},{"group_id":3,"server":["192.168.1.161:6362","192.168.1.30:6362"]},{"group_id":4,"server":["192.168.1.161:6363","192.168.1.30:6364"]},{"group_id":5,"server":["192.168.1.161:6364","192.168.1.30:6364"]},{"group_id":6,"server":["192.168.1.162:6365","192.168.1.31:6365"]},{"group_id":7,"server":["192.168.1.162:6366","192.168.1.31:6366"]},{"group_id":8,"server":["192.168.1.162:6367","192.168.1.31:6367"]},{"group_id":9,"server":["192.168.1.162:6368","192.168.1.31:6368"]},{"group_id":10,"server":["192.168.1.162:6369","192.168.1.31:6369"]}]}
person_in_charge: 某某
           phone: 123456789012
    product_name: cache27

Add Index

mysql> explain select * from codis3 where product_name='cache27';
+------+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+--------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | codis3 | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where |
+------+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> alter table codis3 add index idx_pn(product_name);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from codis3 where product_name='cache27';
+------+-------------+--------+------+---------------+--------+---------+-------+------+-------------+
| id   | select_type | table  | type | possible_keys | key    | key_len | ref   | rows | Extra       |
+------+-------------+--------+------+---------------+--------+---------+-------+------+-------------+
|    1 | SIMPLE      | codis3 | ref  | idx_pn        | idx_pn | 33      | const |    1 | Using where |
+------+-------------+--------+------+---------------+--------+---------+-------+------+-------------+
1 row in set (0.02 sec)

ADD CONSTRAINT

alter table codis3 ADD CONSTRAINT check_info
CHECK(
JSON_TYPE(JSON_QUERY(info, '$.proxy_info')) = 'ARRAY' and
JSON_TYPE(JSON_QUERY(info, '$.dashboard_info')) = 'OBJECT' and
JSON_TYPE(JSON_QUERY(info, '$.group_info')) = 'ARRAY' and
JSON_EXISTS(info, '$.product_name') = 1 and
JSON_EXISTS(info, '$.fe_url') = 1 and
JSON_EXISTS(info, '$.proxy_info') = 1 and
JSON_EXISTS(info, '$.dashboard_info') = 1 and
JSON_EXISTS(info, '$.group_info') = 1);

还有以下校验规则

JSON_TYPE(JSON_VALUE(attr, '$.disks')) = 'INTEGER' and
JSON_EXISTS(attr, '$.video.resolution') = 1 and
JSON_EXISTS(attr, '$.video.aspectRatio') = 1 and
JSON_LENGTH(JSON_QUERY(attr, '$.cuts')) > 0 and
JSON_LENGTH(JSON_QUERY(attr, '$.audio')) > 0);

Function

JSON_VALUE

查询json中的一个value

select JSON_VALUE(info, "$.group_info[0].server[0]") from codis3;
+-----------------------------------------------+
| json_value(info, "$.group_info[0].server[0]") |
+-----------------------------------------------+
| 192.168.1.161:6360                            |
+-----------------------------------------------+

JSON_QUERY

读取一个array

select JSON_QUERY(info, '$.proxy_info')  from codis3;
+---------------------------------------------------------------------------------------------------------------------------------------------------+
| JSON_QUERY(info, '$.proxy_info')                                                                                                                  |
+---------------------------------------------------------------------------------------------------------------------------------------------------+
| [{"proxy_addr":"192.168.1.161:19000","admin_addr":"192.168.1.161:21000"},{"proxy_addr":"192.168.1.162:19000","admin_addr":"192.168.1.162:21000"}] |
+---------------------------------------------------------------------------------------------------------------------------------------------------+

select JSON_QUERY(info, '$.proxy_info[0]')  from codis3;
+-------------------------------------------------------------------------+
| JSON_QUERY(info, '$.proxy_info[0]')                                     |
+-------------------------------------------------------------------------+
| {"proxy_addr":"192.168.1.161:19000","admin_addr":"192.168.1.161:21000"} |
+-------------------------------------------------------------------------+

读取一个object

select JSON_QUERY(info, '$.dashboard_info')  from codis3;
+------------------------------------------------------------------------------------------------------------------+
| JSON_QUERY(info, '$.dashboard_info')                                                                             |
+------------------------------------------------------------------------------------------------------------------+
| {"admin_addr":"192.168.1.124:18080","zookeeper_addr":"192.168.1.124:2181,192.168.1.125:2181,192.168.1.127:2181"} |
+------------------------------------------------------------------------------------------------------------------+

JSON_CONTAINS

判断array中是否包含给定值，包含返回1，否则返回0

select id from codis3 where JSON_CONTAINS(info, '\"192.168.1.161:6360\"', '$.group_info[0].server');
+----+
| id |
+----+
|  1 |
+----+

JSON_OBJECT

将多个列json化

select JSON_OBJECT('id', id, 'product_name', product_name, 'person_in_charge', person_in_charge, 'phone', phone) from codis3;
+-----------------------------------------------------------------------------------------------------------+
| JSON_OBJECT('id', id, 'product_name', product_name, 'person_in_charge', person_in_charge, 'phone', phone) |
+-----------------------------------------------------------------------------------------------------------+
| {"id": 1, "product_name": "cache27", "person_in_charge": "\u67D0\u67D0", "phone": 123456789012}           |
+-----------------------------------------------------------------------------------------------------------+

JSON_INSERT

插入一个field

update codis3 set info=JSON_INSERT(info, "$.test", "192.168.1.161:6364") where id=1;

select JSON_VALUE(info, "$.test") from codis3;
+----------------------------+
| JSON_VALUE(info, "$.test") |
+----------------------------+
| 192.168.1.161:6364         |
+----------------------------+

插入一个array

update codis3 set info=JSON_INSERT(info, "$.test_arr", JSON_ARRAY('English', 'French')) where id=1;

select JSON_QUERY(info, "$.test_arr") from codis3;
+--------------------------------+
| JSON_QUERY(info, "$.test_arr") |
+--------------------------------+
| ["English", "French"]          |
+--------------------------------+

插入一个带array的object

update codis3 set info=JSON_INSERT(info, "$.test_obj", JSON_OBJECT("group_id", 11, "server", JSON_ARRAY('192.168.1.32:6370', '192.168.1.163:6370'))) where id=1;

select JSON_QUERY(info, "$.test_obj") from codis3;
+-------------------------------------------------------------------------+
| JSON_QUERY(info, "$.test_obj")                                          |
+-------------------------------------------------------------------------+
| {"group_id": 11, "server": ["192.168.1.32:6370", "192.168.1.163:6370"]} |
+-------------------------------------------------------------------------+

JSON_ARRAY_APPEND

向array中append值

update codis3 set info=JSON_ARRAY_APPEND(info, "$.group_info", JSON_OBJECT("group_id", 11, "server", JSON_ARRAY('192.168.1.32:6370', '192.168.1.163:6370'))) where id=1;

select JSON_QUERY(info, "$.group_info") from codis3\G
*************************** 1. row ***************************
JSON_QUERY(info, "$.group_info"): [{"group_id": 1, "server": ["192.168.1.161:6360", "192.168.1.30:6360"]}, {"group_id": 2, "server": ["192.168.1.161:6361", "192.168.1.30:6361"]}, {"group_id": 3, "server": ["192.168.1.161:6362", "192.168.1.30:6362"]}, {"group_id": 4, "server": ["192.168.1.161:6363", "192.168.1.30:6364"]}, {"group_id": 5, "server": ["192.168.1.161:6364", "192.168.1.30:6364"]}, {"group_id": 6, "server": ["192.168.1.162:6365", "192.168.1.31:6365"]}, {"group_id": 7, "server": ["192.168.1.162:6366", "192.168.1.31:6366"]}, {"group_id": 8, "server": ["192.168.1.162:6367", "192.168.1.31:6367"]}, {"group_id": 9, "server": ["192.168.1.162:6368", "192.168.1.31:6368"]}, {"group_id": 10, "server": ["192.168.1.162:6369", "192.168.1.31:6369"]}, {"group_id": 11, "server": ["192.168.1.32:6370", "192.168.1.163:6370"]}]
1 row in set (0.00 sec)

JSON_REMOVE

删除某一部分

update codis3 set info=JSON_REMOVE(info, "$.test_obj") where id=1\G