mysql怎么解析json数组

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字符串解析成对应字段

字段名 ：mobile ,内容：{"contactName":"段XX","contactJobTitle":"待确认","contactMobile":"131XXXXXXX"}。

解决方法：JSON_EXTRACT

执行SQL：

查询结果：

结果带引号，并不能真正使用。

解决方法：REPLACE

执行SQL：

查询结果：

问题解决。

sql语句：

SELECT

REPLACE (

JSON_EXTRACT (mobile, '$.contactName'),

'"',

''

) AS 'contactName',

REPLACE (

JSON_EXTRACT (mobile, '$.contactMobile'),

'"',

''

) AS 'contactMobile',

REPLACE (

JSON_EXTRACT (mobile, '$.contactJobTitle'),

'"',

''

) AS 'contactJobTitle'

FROM

cscw_client

WHERE

id = 'XXXXXXXXXXXXXXX'