Mysql如何获取json字符串/数组的值

目录

• Mysql获取json字符串/数组的值
• Mysql获取 json字段中指定的值 ( MySQL 5.7 上下两种方式 )
• 总结

`select json_extract('{"name":"Zhaim","tel":"13240133388"}',"$.tel`");

SELECT id,JSON_LENGTH('[{"id":"10000004555096","name":"1.docx"},{"id":"10000004555098","name":"2.doc"},{"id":"10000004555097","name":"3.html"}]'),F_zwm,F_dhhm FROM `w_b01rzcllc` limit 10;

SELECT id,json_extract('[{"id":"10000004555096","name":"1.docx"},{"id":"10000004555098","name":"2.doc"},{"id":"10000004555097","name":"3.html"}]',"$[0].name"),F_zwm,F_dhhm FROM `w_b01rzcllc` limit 10;

{
    "success": true,
    "code": 0,
    "msg": "操作成功",
    "data": {
        "access_token": "333d8c09-e522-4585-8cca-1d9c66e5cece",
        "token_type": "bearer",
        "refresh_token": "48a3e598-0e3e-4739-9e57-b301c1e3529d",
        "expires_in": 3599,
        "scope": "app"
    }
}

select replace(json_extract(remark, '$.data.budgetNum'), '"', '')
from sys_user;

select REPLACE(
                       remark,
                       CONCAT(
                               SUBSTRING_INDEX(remark, '"access_token":', 1),
                               '"access_token":"'
                           ),
                       ''
                   ) from sys_user;