mysql解析json/数组

mysql在5.7开始支持json解析了 也可以解析数组哦!

直接上demo:

 SELECT Substr(col, 2, Length(col) - 2), Length(col)
         FROM   (SELECT Json_extract(Json_extract(Json_extract(state, "$.tpl"),"$.items"
                            ), "$[0].url")
               AS col
        FROM   page
        ORDER  BY id DESC
        LIMIT  100) t;

JSON_EXTRACT可以解析sql , tpl就是你json的key值

如果是数组,用$[*].url 或者 $[0].url 获取全部的value 或者某个下标的url

下面这个demo可以直接复制到sql运行:

 select JSON_EXTRACT(JSON_EXTRACT(JSON_EXTRACT('{"tpl":{"items":[{"type":"image","config":{"expandable":true,"linkAble":true},"url":"https://fs.esf.fangdd.net/test/FiZ0OtkhTZoD7fOtkp55SnuLGiKu.png?imageView2/2/w/750","id":1542348252537},{"type":"image","config":{"expandable":true,"linkAble":true},"url":"https://fs.esf.fangdd.net/test/FlR1VDQWEzD406NosLFrJUez4g_X.png?imageView2/2/w/750","id":1542348263477},{"type":"image","config":{"expandable":true,"linkAble":true},"url":"https://fs.esf.fangdd.net/test/FhMuYkWvnoMbv8I1dlQbm1KaX5Kn.png?imageView2/2/w/750","id":1542348269599},{"type":"image","config":{"expandable":true,"linkAble":true},"url":"https://fs.esf.fangdd.net/test/FlgR4IUNElPbcgjN2re_9A8jX30v.png?imageView2/2/w/750","id":1542348276124},{"type":"image","config":{"expandable":true,"linkAble":true},"url":"https://fs.esf.fangdd.net/test/FpXF8ETHxU8aqriiKbsYDjnu2Xd5.png?imageView2/2/w/750","id":1542348282561},{"type":"image","config":{"expandable":true,"linkAble":true},"url":"https://fs.esf.fangdd.net/test/FkUz5m7Jd6kE2slSyreDucozc3XH.png?imageView2/2/w/750","id":1542348288150,"link":"http://www.baidu.com"}],"bottomItems":[],"title":"demo2","description":"","wxLogo":"","bodyStyleInline":{},"bg":"","bgType":"","bottomStyleInline":{},"bottomBg":"","bottomBgType":"","uuid":"aaef8dfe-256a-4559-aec9-95d1fcdcf830","activeItemsName":"items","activeImgType":"","authInfo":{"role_list":[{"name":"test","access_key_list":[]},{"name":"审核人员","access_key_list":[]}],"city_list":[],"userId":3108779,"userName":"zhangyusheng","email":"zhangyusheng@xxx.com","mobile":"123123","trueName":"张昱升","isEmployee":true}}}', "$.tpl"), "$.items"), "$[0].url");

我们来分析一下

原始json为

{
    "tpl":{
        "items":[
            {
                "type":"image",
                "config":{
                    "expandable":true,
                    "linkAble":true
                },
                "url":"https://fs.esf.fangdd.net/test/FiZ0OtkhTZoD7fOtkp55SnuLGiKu.png?imageView2/2/w/750",
                "id":1542348252537
            },
            {
                "type":"image",
                "config":{
                    "expandable":true,
                    "linkAble":true
                },
                "url":"https://fs.esf.fangdd.net/test/FlR1VDQWEzD406NosLFrJUez4g_X.png?imageView2/2/w/750",
                "id":1542348263477
            },
            {
                "type":"image",
                "config":{
                    "expandable":true,
                    "linkAble":true
                },
                "url":"https://fs.esf.fangdd.net/test/FhMuYkWvnoMbv8I1dlQbm1KaX5Kn.png?imageView2/2/w/750",
                "id":1542348269599
            },
            {
                "type":"image",
                "config":{
                    "expandable":true,
                    "linkAble":true
                },
                "url":"https://fs.esf.fangdd.net/test/FlgR4IUNElPbcgjN2re_9A8jX30v.png?imageView2/2/w/750",
                "id":1542348276124
            },
            {
                "type":"image",
                "config":{
                    "expandable":true,
                    "linkAble":true
                },
                "url":"https://fs.esf.fangdd.net/test/FpXF8ETHxU8aqriiKbsYDjnu2Xd5.png?imageView2/2/w/750",
                "id":1542348282561
            },
            {
                "type":"image",
                "config":{
                    "expandable":true,
                    "linkAble":true
                },
                "url":"https://fs.esf.fangdd.net/test/FkUz5m7Jd6kE2slSyreDucozc3XH.png?imageView2/2/w/750",
                "id":1542348288150,
                "link":"http://www.baidu.com"
            }
        ],
        "bottomItems":[
 
        ],
        "title":"demo2",
        "description":"",
        "wxLogo":"",
        "bodyStyleInline":{
 
        },
        "bg":"",
        "bgType":"",
        "bottomStyleInline":{
 
        },
        "bottomBg":"",
        "bottomBgType":"",
        "uuid":"aaef8dfe-256a-4559-aec9-95d1fcdcf830",
        "activeItemsName":"items",
        "activeImgType":"",
        "authInfo":{
            "role_list":[
                {
                    "name":"test",
                    "access_key_list":[
 
                    ]
                },
                {
                    "name":"审核人员",
                    "access_key_list":[
 
                    ]
                }
            ],
            "city_list":[
 
            ],
            "userId":3108779,
            "userName":"zhangyusheng",
            "email":"zhangyusheng@xxx.com",
            "mobile":"23123",
            "trueName":"张昱升",
            "isEmployee":true
        }
    }
}

$.tpl就是获取tpl这个键key

$[0].url 就是获取[{url:1},{url:2}] 这个数组第一个对象的url值 也就是1

已标记关键词 清除标记
相关推荐
因为一些原因,想尝试在mysql中使用json数组,循环取值时比较方便,但是在测试时发现取出来的值可以显示正确,但是用在函数中时却无法得到正确的结果,用cast函数转换后依旧无法得出正确的结果,测试代码如下: ``` SELECT JSON_ARRAYAGG(JSON_OBJECT('rulename',rulename,'daynum',daynum,'qcrule',qcrule,'qcjg',qcjg)) INTO @jsonstr from f_calrule WHERE state = 1 AND calid = 1 ORDER BY id; SELECT JSON_EXTRACT(@jsonstr,'$[4].daynum') INTO @daynum; SELECT JSON_EXTRACT(@jsonstr,'$[4].qcrule') INTO @qcrule; SELECT JSON_EXTRACT(@jsonstr,'$[4].qcjg') INTO @qcjg; SELECT cast(@daynum as UNSIGNED INTEGER) INTO @daynum1; SELECT cast(@qcrule as CHAR) INTO @qcrule1; SELECT cast(@qcjg as UNSIGNED INTEGER) INTO @qcjg1; SELECT f_calnum(@daynum,@qcrule,@qcjg,'132312428485653536','2020-06-08') INTO @r; SELECT f_calnum(@daynum1,@qcrule1,@qcjg1,'132312428485653536','2020-06-08') INTO @r1; SELECT f_calnum(90,"天",1,'132312428485653536','2020-06-08') INTO @r2; SELECT @jsonstr,@rulename,@daynum,@daynum1,@qcrule,@qcrule1,@qcjg,@qcjg1,@r,@r1,@r2; ``` 测试结果如下: ![图片说明](https://img-ask.csdn.net/upload/202006/09/1591715419_938435.png) 如图,@r2是正确结果,是直接将取出来的值填入函数的参数表中得到的。从json数组中提取出来的结果直接放在函数参数表中以及转换完类型再放入函数参数表中得到的计算结果@r和@r1都为0,显然是不对的。但是在结果中可以看到从json数组中取出来的@daynum、@qcrule、@qcjg的值是没有问题的,我不知道究竟是数据类型的问题还是其他原因导致结果的错误,如果是数据类型的问题,为什么用cast转换类型后依旧不能得到正确的结果呢?求大佬解惑! PS:附上我用的函数的定义: CREATE DEFINER=`root`@`localhost` FUNCTION `f_calnum`(daynum INT,qcrule varchar(255),qcjg INT,puserid varchar(255),caldate date) RETURNS int(11) 测试软件如下: navicat版本为:navicat premium 12.0.11 x64 mysql版本为:mysql-5.7.23-winx64
©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页