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

Question

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().

Answer 1

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.