dongtan7998 2016-06-25 05:27
浏览 69
已采纳

我想从json数据中计算唯一键,该数据存储在多行中

I have multiple json rows in a table

For Example

row1 -> {"json_key_1":{"ele":"value"},"json_key2":{"ele":"value""}}

row2 -> {"json_key_10":{"ele":"value"},"json_key20":{"ele":"value""}}

row3 -> {"json_key_1":{"ele":"value"},"json_key20":{"ele":"value""}}

row4 -> {"json_key_2":{"ele":"value"},"json_key30":{"ele":"value""}}

In these four json row i have to count unique keys

For eg. "json_key_2" which is exists in row1->josn and row4->json

So i want unique keys from all json by stored procedure or some Query which should count these key only once even present in multiple json rows

each key count only once json_key_1,json_key2,json_key_10,json_key_20,json_key_30

in this example KEY COUNT Result should be : 5

IN MySQL 5.7 we have functions to perform operation on JSON

like : json_merge(), json_length() how to use them to achive this result.

Is this possible in MYSQL ?

I have created a stored procedure to get result

SELECT `json_row` INTO @ar1
FROM `table` 
WHERE row_id=1;

SELECT `json_row` INTO @ar2
FROM `table` 
WHERE row_id=2;

SELECT `json_row` INTO @ar3
FROM `table` 
WHERE row_id=3;

SELECT `json_row` INTO @ar4
FROM `table` 
WHERE row_id=4;

SELECT json_merge(@ar1,@ar2,@ar3,@ar4) INTO @result;

SELECT json_length(@result);

This Query return in result json_keys length which is : 5

I want to improve this query

Can anyone suggest how to make better select query because in this i am selecting each json_row one by one by row_id.

But if i will use some user_id in where clause instead of row_id

SELECT Query will return 4 rows of that user and then how will store 4 rows into variables to perform json_merge() and json_length().

  • 写回答

1条回答 默认 最新

  • douxie9347 2016-06-27 09:47
    关注

    Finally i got the Solution what i am looking for i was trying to count json_key from multiple json rows and now i can do this with the help of MYSQL CURSOR.

    DECLARE curs_row CURSOR FOR SELECT `json_data` FROM `table`  where `user_id`=26972;
    

    curs_row will be process multiple rows one by one into loop and i can merge and calculate json length with this stored procedure with single SELECT Query.

    Below is complete Query :

    BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE email_list JSON DEFAULT '{}';
    DECLARE get_list JSON DEFAULT '{}';
    DECLARE merge_email_list JSON DEFAULT '{}';
    DECLARE curs_row CURSOR FOR SELECT `json_data` FROM `table`  where `user_id`=26972;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    OPEN curs_row;
    read_loop: LOOP
    FETCH curs_row INTO email_list;
    IF done THEN
    LEAVE read_loop;
    END IF;
    SELECT email_list INTO get_list;
    SELECT json_merge(get_list,merge_email_list) INTO merge_email_list;
    END LOOP;
    SELECT json_length(merge_email_list);
    CLOSE curs_row;
    END
    

    So the solution is cursor loop i am adding this solution here maybe this will help others.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题