MySQL 解析普通JSON,解析JSON数组案例demo

发布时间:2019-11-14作者:laosun阅读(6067)

MySQL

    首先呢创建一张表

    CREATE TABLE `t_json`  (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `json` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

    插入两条测试数据

    INSERT INTO `t_json`(`id`, `json`) VALUES (1, '{\"id\": \"100\", \"name\": \"普通json\"}');
    INSERT INTO `t_json`(`id`, `json`) VALUES (2, '[{\"id\": \"1\", \"name\": \"json数组1\"}, {\"id\": \"2\", \"name\": \"json数组2\"}]');


    解析json

    我们使用 MySQL JSON_EXTRACT 语法

    1. 对于普通json的解析方法如下:

    select JSON_EXTRACT(json, '$.name') from t_json where `id` = 1;
    #输出:"普通json"
    为了更好的应用可以使用截取前后两位,将双引号去掉。

    2. 解析json数组的方法有点复杂:

    首先看我插入进去的数组格式:[{"id": "1", "name": "json数组1"}, {"id": "2", "name": "json数组2"}]

    没有数组key值。 所以我们需要将其拼凑成完整json格式。

    比如拼凑成: {"item":[{"id": "1", "name": "json数组1"}, {"id": "2", "name": "json数组2"}]} 

    CONCAT('{"item":',json,'}')

    然后我们再使用 JSON_EXTRACT 语法进行解析。如下所示:

    # 第一个JSON_EXTRACT是为了解析json,指定我们的key,就是要解析的数组。
    # 第二个JSON_EXTRACT是解析指定key的数组。 这块我使用$[*].name:表示循环解析所有的name值,如果要解析第一个name值,使用$[0].name
    # 如果要解析id,就将$[*].name 换成 $[*].id
    select JSON_EXTRACT(JSON_EXTRACT(CONCAT('{"item":',json,'}'), '$.item'), '$[*].name') from t_json where `id` = 2;
    
    # 以上SQL执行输出:["json数组1", "json数组2"]

    看样子也不是我们想要的。json数组1,json数组2 也比 ["json数组1", "json数组2"] 这种格式要好。所以下边我们进行转换。

    转换的方式是使用replace语法,进行替换。

    这块使用有点绕,其实就是使用了三次repalce,第一次是将 [" 替换掉, 第二次是将", " 替换掉,第三次是将 "] 替换掉。如下所示:

    select REPLACE(REPLACE(REPLACE(JSON_EXTRACT(JSON_EXTRACT(CONCAT('{"item":',json,'}'), '$.item'), '$[*].name'), '["', ''), '", "', ','), '"]', '') from t_json where `id` = 2;
    #输出结果:json数组1,json数组2

    这时候可能有的业务要求特殊,需要将上边的值换成列,也就是我们所说的以逗号分隔,行转列。 这个我们需要使用 mysql.help_topic表来协助完成。

    select substring_index(substring_index(REPLACE(REPLACE(REPLACE(JSON_EXTRACT(JSON_EXTRACT(CONCAT('{"item":',j.json,'}'), '$.item'), '$[*].name'), '["', ''), '", "', ','), '"]', ''),',',ht.help_topic_id+1),',',-1) as 'value值'
    from  t_json j join mysql.help_topic ht
    on ht.help_topic_id < (length(REPLACE(REPLACE(REPLACE(JSON_EXTRACT(JSON_EXTRACT(CONCAT('{"item":',j.json,'}'), '$.item'), '$[*].name'), '["', ''), '", "', ','), '"]', '')) - length(replace(REPLACE(REPLACE(REPLACE(JSON_EXTRACT(JSON_EXTRACT(CONCAT('{"item":',j.json,'}'), '$.item'), '$[*].name'), '["', ''), '", "', ','), '"]', ''),',',''))+1)
    where j.id = 2
    
    # 输出结果:
    json数组1
    json数组2

    或者换种写法,写成以下这种形似,更容易理解一些

    select substring_index(substring_index(tmp.jsonstr,',',ht.help_topic_id+1),',',-1) as 'value值' from (select REPLACE(REPLACE(REPLACE(JSON_EXTRACT(JSON_EXTRACT(CONCAT('{"item":',json,'}'), '$.item'), '$[*].name'), '["', ''), '", "', ','), '"]', '') as jsonstr from t_json j where j.id = 2) as tmp
    join mysql.help_topic ht on ht.help_topic_id < (length(tmp.jsonstr) - length(replace(tmp.jsonstr,',',''))+1)


8 +1

版权声明

 数据库  mysql

 请文明留言

0 条评论