Mysql json

 最基础的保存和取数，适合科普给没用过json的：

#如果t_asset_bussiness_json表不存在type=1,property_order_id=1的数据：
insert into t_asset_bussiness_json (type, property_order_id, order_id, bussiness_json)
values (1,1,null,'{"actualfee":153.05,"expextfee":200.00}');

#如果表中存在type=1,property_order_id=1 的数据
update t_asset_bussiness_json
set bussiness_json= ifnull(
    json_set(bussiness_json,'$.actualfee',153.05,'$.expextfee',200.00), #bussiness_json中已有数据
    json_object('actualfee',153.05,'expextfee',200.00)) #bussiness_json字段为null
where type=1 and property_order_id = 1;

#取数据
select json_unquote(json_extract(bussiness_json,'$.actualfee')) from t_asset_bussiness_json;

#更新数据，将actualfee更新为5000
update t_asset_bussiness_json
set bussiness_json= json_set(bussiness_json,'$.actualfee',5000.00)
where type=1 and property_order_id = 1;

p1|p2|p3

匹配 p1 或 p2 或 p3。例如，'z|food' 能匹配 "z" 或 "food"。'(z|f)ood' 则匹配 "zood" 或 "food"。

使用正则表达式在json数组中匹配多个值：

设置变量： 

查询：

select * from tb_workbussinessjsoninfo where
(BussinessJson->'$[*]') regexp '0101|0105';

 结果：

方法正确。

JSON值 添加多值，当JSON为空时，使用json_object做初始化（json_set无法写入空字符串）:

set BussinessJson= ifnull(
        json_set(BussinessJson, '$.PolicyBusinessType', a.PolicyBusinessType, '$.PolicyFileType', PolicyFileType,
                 '$.PolicyFileTypeName', PolicyFileTypeName),
        JSON_OBJECT('PolicyBusinessType', a.PolicyBusinessType, 'PolicyFileType', PolicyFileType, 'PolicyFileTypeName',
                    PolicyFileTypeName))

JSON数组 的使用和修改:

#外访人记录 结构格式
"VisitorInfoHis" :{["LoginName":'ZR20000001',"UserName":'员工1'],["LoginName":'ZR20000002',"UserName":'员工2']}

1、新增VisitorInfoHis（外访人记录）

update tb_workbussinessjsoninfo
SET BussinessJson=json_set(BussinessJson,'$.VisitorInfoHis',json_object('LoginName','ZR20000002','UserName','员工2'))
where id = 9;

2、新增LoginName、UserName（外访人）

update tb_workbussinessjsoninfo
SET BussinessJson=json_array_insert(BussinessJson,'$.VisitorInfoHis[0]',json_object('LoginName','ZR20040021','UserName','尚小昊1'))
where id = 9;

3、删除最新的LoginName、UserName（外访人）

update tb_workbussinessjsoninfo
SET BussinessJson=JSON_REMOVE(BussinessJson, '$.VisitorInfoHis[0]')
where id = 9;

4、更新最新的LoginName、UserName（外访人）

update tb_workbussinessjsoninfo
SET BussinessJson=JSON_set(BussinessJson, '$.VisitorInfoHis[0]',json_object('LoginName','ZR20000005','UserName','员工5'))
where id = 9;

5、查询

select *
from tb_afcnegotiationrecordinfo a
where a.deleted = 0
and exists(
    select 1
    from tb_workbussinessjsoninfo b
    where a.NegotiatorName member of(BussinessJson->'$.VisitorInfoHis[*].UserName')
    )
and a.WorkOrderId in('111111111')

6.无外层直接存json数组，空则新增，有则插入$[0]

UPDATE
                    tb_workorderevaluateinfo
                    SET MessageJson = IFNULL(json_array_insert(MessageJson,'$[0]',JSON_OBJECT('UserType', @UserType,'LoginName', @LoginName,'UserName',@UserName,'Message',@Message,'MessageTime',@MessageTime)),
                    json_array(JSON_OBJECT('UserType',@UserType, 'LoginName',@LoginName,'UserName',@UserName,'Message',@Message,'MessageTime',@MessageTime)))
                    WHERE WorkOrderId ='1111111111';

7.有外层存json数组，空则更新，有则插入$[100],不支持字段不为空的初始化
UPDATE
    tb_workbussinessjsoninfo
SET BussinessJson = IFNULL(json_array_insert(BussinessJson, '$.OilCutOffInfo[100]',
                                             JSON_OBJECT('MaterialNo', '@MaterialNo', 'OilCutOff', '@OilCutOff')),
                           json_object('OilCutOffInfo', json_array(
                                   json_object('MaterialNo', '@MaterialNo', 'OilCutOff', '@OilCutOff'))))
WHERE WorkOrderId = '3';
8.
#初始化
update tb_workbussinessjsoninfo
set BussinessJson= ifnull(json_set(BussinessJson,'$.OilCutOffInfo',json_array(
    json_object('MaterialNo','650','OilCutOff',1),
    json_object('MaterialNo','680','OilCutOff',0)
    )),
    json_object('OilCutOffInfo',json_array(
    json_object('MaterialNo','650','OilCutOff',1),
    json_object('MaterialNo','680','OilCutOff',0)
    )))
where id =8;

#新增设备
update tb_workbussinessjsoninfo
set BussinessJson= json_array_insert(BussinessJson,'$.OilCutOffInfo[100]',json_object('MaterialNo','780','OilCutOff',0))
where workorderid='';

#删除所有
update tb_workbussinessjsoninfo
SET BussinessJson=JSON_REMOVE(BussinessJson, '$.OilCutOffInfo')
where '780' member of(BussinessJson->'$.OilCutOffInfo[*].MaterialNo');
#删除设备编码为780的选项#json_search 仅支持查字符串类型的数据位置，不支持整数
update tb_workbussinessjsoninfo
SET BussinessJson=
    JSON_REMOVE(BussinessJson, concat('$.OilCutOffInfo[',substr(json_search(json_extract(BussinessJson,'$.OilCutOffInfo'),'one','780'),4,1),']'))
where '780' member of(BussinessJson->'$.OilCutOffInfo[*].MaterialNo');

#更新设备编码为780的选项的 OilCutOff 数值
update tb_workbussinessjsoninfo
SET BussinessJson=json_replace(BussinessJson,
    concat('$.OilCutOffInfo[',substr(json_search(json_extract(BussinessJson,'$.OilCutOffInfo'),'one','780'),4,1),'].OilCutOff'),1)
where '780' member of(BussinessJson->'$.OilCutOffInfo[*].MaterialNo');

JSON存储 因为存入转义字符导致直接取key获取不到，需要json函数过滤一遍:

SELECT DISTINCT WorkOrderId,ReceiveDetailJson,ReceiveDetailJson->>'$.ReceiveLog' AS ReceiveLog,
JSON_UNQUOTE(JSON_EXTRACT((ReceiveDetailJson->>'$.ReceiveLog'),'$[0].ReceiveFileList[0].fileName')) AS ReceiveTime
FROM tb_vhsworkfilereceiveinfo 
WHERE ReceiveDetailJson IS NOT NULL
AND ReceiveDetailJson->>'$.ReceiveLog' IS NOT NULL;