MariaDB [test_json_fields]>select*from computer;+------+------+-------------------------+| id | name | info |+------+------+-------------------------+|1| lucy | {"cpu": 4,"memory": 8} |+------+------+-------------------------+1rowinset(0.001 sec)
MariaDB [test_json_fields]>
五:操作info对象
(1)查询info信息中的cpu值
MariaDB [test_json_fields]>select JSON_EXTRACT(info,'$.cpu')from computer where id =1;+-----------------------------+| JSON_EXTRACT(info,'$.cpu')|+-----------------------------+|4|+-----------------------------+1rowinset(0.001 sec)
MariaDB [test_json_fields]>
(2)根据info信息中的cpu值查询符合的对象
MariaDB [test_json_fields]>select*from computer where JSON_EXTRACT(info,'$.cpu')=4;+------+------+-------------------------+| id | name | info |+------+------+-------------------------+|1| lucy | {"cpu": 4,"memory": 8} |+------+------+-------------------------+1rowinset(0.001 sec)
MariaDB [test_json_fields]>
(3)修改info信息中的cpu值为8
JSON_REPLACE or JSON_SET
MariaDB [test_json_fields]>update computer set info = JSON_REPLACE(info,'$.cpu',8)where id =1;
Query OK,1row affected (0.299 sec)Rowsmatched: 1 Changed: 1Warnings: 0
MariaDB [test_json_fields]>select*from computer;+------+------+-------------------------+| id | name | info |+------+------+-------------------------+|1| lucy | {"cpu": 8,"memory": 8} |+------+------+-------------------------+1rowinset(0.018 sec)
MariaDB [test_json_fields]>
MariaDB [test_json_fields]>update computer set info = JSON_SET(info,'$.cpu',4)where id =1;
Query OK,1row affected (0.003 sec)Rowsmatched: 1 Changed: 1Warnings: 0
MariaDB [test_json_fields]>select*from computer;+------+------+-------------------------+| id | name | info |+------+------+-------------------------+|1| lucy | {"cpu": 4,"memory": 8} |+------+------+-------------------------+1rowinset(0.000 sec)
MariaDB [test_json_fields]>
(4)在info信息中新增一条disk信息
JSON_INSERT or JSON_SET
说明:如果给的键在info中,则根据值修改,否则,新增
MariaDB [test_json_fields]>update computer set info = JSON_INSERT(info,'$.disk',100)where id =1;
Query OK,1row affected (0.004 sec)Rowsmatched: 1 Changed: 1Warnings: 0
MariaDB [test_json_fields]>select*from computer;+------+------+--------------------------------------+| id | name | info |+------+------+--------------------------------------+|1| lucy | {"cpu": 4,"memory": 8,"disk": 100} |+------+------+--------------------------------------+1rowinset(0.001 sec)
MariaDB [test_json_fields]>
(5)删除info信息中的disk信息
MariaDB [test_json_fields]>update computer set info = JSON_REMOVE(info,'$.disk')where id =1;
Query OK,1row affected (0.003 sec)Rowsmatched: 1 Changed: 1Warnings: 0
MariaDB [test_json_fields]>select*from computer;+------+------+-------------------------+| id | name | info |+------+------+-------------------------+|1| lucy | {"cpu": 4,"memory": 8} |+------+------+-------------------------+1rowinset(0.000 sec)
MariaDB [test_json_fields]>
六:操作深层对象
(1)在info信息中增加一个对象,保存网络信息
MariaDB [test_json_fields]>update computer set info = JSON_INSERT(info,'$.network', JSON_OBJECT('in','521kbps','out','128kbps'))where id =1;
Query OK,1row affected (0.003 sec)Rowsmatched: 1 Changed: 1Warnings: 0
MariaDB [test_json_fields]>select*from computer;+------+------+-------------------------------------------------------------------------+| id | name | info |+------+------+-------------------------------------------------------------------------+|1| lucy | {"cpu": 4,"memory": 8,"network": {"in": "521kbps","out": "128kbps"}} |+------+------+-------------------------------------------------------------------------+1rowinset(0.000 sec)
MariaDB [test_json_fields]>
(2)将info信息中的网络对象信息中的out值更新为256kbps,如果该值为128kbps的话
MariaDB [test_json_fields]>update computer set info = JSON_SET(info,'$.network.out','256kbps'))where JSON_EXTRACT(info,'$.network.out')='128kbps';
Query OK,1row affected (0.003 sec)Rowsmatched: 1 Changed: 1Warnings: 0
MariaDB [test_json_fields]>select*from computer;+------+------+-------------------------------------------------------------------------+| id | name | info |+------+------+-------------------------------------------------------------------------+|1| lucy | {"cpu": 4,"memory": 8,"network": {"in": "521kbps","out": "256kbps"}} |+------+------+-------------------------------------------------------------------------+1rowinset(0.000 sec)
MariaDB [test_json_fields]>
(3)将info信息中的网络对象信息中的out信息删除
MariaDB [test_json_fields]>update computer set info = JSON_REMOVE(info,'$.network.out'))where id =1;
Query OK,1row affected (0.003 sec)Rowsmatched: 1 Changed: 1Warnings: 0
MariaDB [test_json_fields]>select*from computer;+------+------+-------------------------------------------------------------------------+| id | name | info |+------+------+-------------------------------------------------------------------------+|1| lucy | {"cpu": 4,"memory": 8,"network": {"in": "521kbps"}} |+------+------+-------------------------------------------------------------------------+1rowinset(0.000 sec)
MariaDB [test_json_fields]>
(4)在info信息中的网络对象信息中增加out信息
MariaDB [test_json_fields]>update computer set info = JSON_INSERT(info,'$.network.out','128kbps'))where id =1;
Query OK,1row affected (0.003 sec)Rowsmatched: 1 Changed: 1Warnings: 0
MariaDB [test_json_fields]>select*from computer;+------+------+-------------------------------------------------------------------------+| id | name | info |+------+------+-------------------------------------------------------------------------+|1| lucy |{"cpu": 4,"memory": 8,"network": {"in": "521kbps","out": "256kbps"}} |+------+------+-------------------------------------------------------------------------+1rowinset(0.000 sec)
MariaDB [test_json_fields]>
(5)在info信息中增加一个数组,保存网卡名称
MariaDB [test_json_fields]>update computer set info = JSON_INSERT(info,'$.nic', JSON_ARRAY('eth0','ens33'))where id =1;
Query OK,1row affected (0.003 sec)Rowsmatched: 1 Changed: 1Warnings: 0
MariaDB [test_json_fields]>select*from computer;+------+------+-------------------------------------------------------------------------+| id | name | info |+------+------+-------------------------------------------------------------------------+|1| lucy | {"cpu": 4,"memory": 8,"network": ["eth0","ens33"]} |+------+------+-------------------------------------------------------------------------+1rowinset(0.000 sec)
MariaDB [test_json_fields]>
(6)将info信息中网卡名称数组中的第一个网卡名称修改为eth1
MariaDB [test_json_fields]>update computer set info = JSON_INSERT(info,'$.nic[0]','eth1')where id =1;
Query OK,1row affected (0.003 sec)Rowsmatched: 1 Changed: 1Warnings: 0
MariaDB [test_json_fields]>select*from computer;+------+------+-------------------------------------------------------------------------+| id | name | info |+------+------+-------------------------------------------------------------------------+|1| lucy | {"cpu": 4,"memory": 8,"network": ["eth1","ens33"]} |+------+------+-------------------------------------------------------------------------+1rowinset(0.000 sec)
MariaDB [test_json_fields]>
(7)删除info信息中网卡名称数组中的最后一个网卡名称
MariaDB [test_json_fields]>update computer set info = JSON_REMOVE(info,'$.nic[1]')where id =1;
Query OK,1row affected (0.003 sec)Rowsmatched: 1 Changed: 1Warnings: 0
MariaDB [test_json_fields]>select*from computer;+------+------+-------------------------------------------------------------------------+| id | name | info |+------+------+-------------------------------------------------------------------------+|1| lucy | {"cpu": 4,"memory": 8,"network": ["eth1"]} |+------+------+-------------------------------------------------------------------------+1rowinset(0.000 sec)
MariaDB [test_json_fields]>
(8)在info信息中网卡名称数组增加一个网卡名称eth0,位于第一位置
MariaDB [test_json_fields]>update computer set info = JSON_ARRAY_INSERT(info,'$.nic[0]','eth0')where id =1;
Query OK,1row affected (0.003 sec)Rowsmatched: 1 Changed: 1Warnings: 0
MariaDB [test_json_fields]>select*from computer;+------+------+-------------------------------------------------------------------------+| id | name | info |+------+------+-------------------------------------------------------------------------+|1| lucy | {"cpu": 4,"memory": 8,"network": ["eth0","eth1"]} |+------+------+-------------------------------------------------------------------------+1rowinset(0.000 sec)
MariaDB [test_json_fields]>
(9)在info信息中网卡名称数组追加一个网卡名称ens33,位于最后
MariaDB [test_json_fields]>update computer set info = JSON_ARRAY_APPEND(info,'$.nic','ens33')where id =1;
Query OK,1row affected (0.003 sec)Rowsmatched: 1 Changed: 1Warnings: 0
MariaDB [test_json_fields]>select*from computer;+------+------+-------------------------------------------------------------------------+| id | name | info |+------+------+-------------------------------------------------------------------------+|1| lucy | {"cpu": 4,"memory": 8,"network": ["eth0","eth1","ens33"]} |+------+------+-------------------------------------------------------------------------+1rowinset(0.000 sec)
MariaDB [test_json_fields]>