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

2024-08-09 10:08

短信预约 -IT技能 免费直播动态提醒

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

单个json的，获取值使用json_extract

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

数组的json的，获取数组的长度使用JSON_LENGTH

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;

数组的json的，获取数组的值使用json_extract

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;

Mysql获取 json字段中指定的值 ( MySQL 5.7 上下两种方式 )

JSON：

{
    "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"
    }
}

MySQL 5.7 以上可直接使用函数 JSON_EXTRACT 获取

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

结果：

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

MySQL 5.7 以下可利用分割函数进行获取

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