这样一组json数据,要将这组数据取出相加,创建一个存储过程来实现
CREATE PROCEDURE jia(ids int)
BEGIN
DECLARE i int DEFAULT 0;
DECLARE v_length int;
DECLARE v_a DOUBLE;
DECLARE
sun DOUBLE DEFAULT 0;
SELECT JSON_LENGTH(json) INTO v_length FROM id_json WHERE id = ids;
WHILE i<v_length DO
SET @sql = CONCAT('SELECT json -> \'$[',i,']\' INTO @v_a from id_json WHERE id = ?');
set @param1 = ids;
PREPARE stmt FROM @sql;
EXECUTE stmt using @param1;
deallocate prepare stmt;
SELECT @v_a;
set sun = sun + @v_a;
set i = i +1;
END WHILE;
select sun;
END
如果里面存储的是一个json对象需要拿出对象的某一个属性的值相加
那就在动态循环拿出时点出某一个属性