>我正在使用MySQL JSON对象,保存在数据库中的对象如下
{"additionalSlots": [{"date": null, "count": 0}, {"date": 1564127223311, "count": 1000}]}
我正在尝试做的是读取所有日期,如果日期大于当前日期,则将特定对象的计数更新为 0。
有什么方法可以做到这一点吗?
更新:
我能够循环使用这个
DELIMITER $$
DROP PROCEDURE IF EXISTS test_mysql_while_loop$$
CREATE PROCEDURE test_mysql_while_loop()
BEGIN
DECLARE x INT;
DECLARE str VARCHAR(255);
DECLARE json VARCHAR(4000);
DECLARE w INT;
SET x = 0;
SET str = '';
SELECT
JSON_LENGTH(additionalslots ,"$.additionalSlots") INTO w
FROM
user_detail;
WHILE x < w DO
SELECT
JSON_EXTRACT(additionalslots ,CONCAT('$.additionalSlots[',x,']')) INTO json
FROM
user_detail;
SET x = x + 1;
END WHILE;
SELECT json;
END$$
DELIMITER ;
CALL test_mysql_while_loop();
现在我需要做的是在日期已通过或日期为空时删除该值。我怎样才能做到这一点?
我期待的结果是
由此
{"additionalSlots": [{"date": null, "count": 0}, {"date": 1564127223311, "count": 1000}]}
对此
{"additionalSlots": [{"date": 1564127223311, "count": 1000}]}
我能够使用
DELIMITER $$
DROP PROCEDURE IF EXISTS removeadditionalhits$$
CREATE PROCEDURE removeadditionalhits()
BEGIN
DECLARE x INT;
DECLARE w INT;
SET x = 0;
SET str = '';
SELECT
JSON_LENGTH(additionalhits ,"$.additionalSlots") INTO w
FROM
user_detail;
WHILE x < w DO
SELECT
JSON_EXTRACT(additionalhits ,CONCAT('$.additionalSlots[',x,']'))
FROM
user_detail
WHERE
now() > FROM_UNIXTIME(JSON_EXTRACT(additionalhits ,CONCAT('$.additionalSlots[',x,'].date'))/1000) ;
UPDATE
user_detail
SET
additionalhits = JSON_REMOVE(additionalhits ,CONCAT('$.additionalSlots[',x,']'))
WHERE
now() > FROM_UNIXTIME(JSON_EXTRACT(additionalhits ,CONCAT('$.additionalSlots[',x,'].date'))/1000) ;
SET x = x + 1;
END WHILE; END$$
DELIMITER ;
CALL removeadditionalslots();