如何用MYSQL与JSON中的数组和字段中的值？

Question

我有一个表，其中的字段有时在JSON中有多个数组。

MYSQL 8.0.11

DLL

CREATE TABLE T
    (`user` varchar(4), `sales` int, `reference` varchar(400) DEFAULT NULL, `quantity` float DEFAULT NULL, `orders` int)
;

数据

INSERT INTO T
    (`user`, `sales`, `reference`, `quantity`, `orders`)
VALUES
    ('xx01', 100, '[{"productid":"80000052","quantity":"1","reference":"ML-41"},{"quantity":1,"reference":"ML-32","productid":"ML-52"},{"productid":"80000052","quantity":3,"reference":"ML-11"}]', 0, 0),
    ('xx02', 200, '[{"productid":"80000052","quantity":2}]', 0, 0),
    ('xx02', 400, '[{"productid":"80000052","quantity":3}]', 0, 0),
    ('xx03', 300, '[{"productid":"80000052","quantity":4}]', 0, 0),
    ('xx03', 500, '[{"productid":"80000052","quantity":5}]', 0, 0)
;

以下查询使用"reference“字段JSON数组中的"quantity”值更新字段"quantity“：

UPDATE T t2, 
( SELECT sales, quant FROM T, JSON_TABLE(T.reference,
    "$[*]" COLUMNS(
        quant VARCHAR(400) PATH "$.quantity"
        )
    ) AS jt1
) t1
SET t2.quantity = t1.quant
WHERE t1.sales = t2.sales;

查询接受第一行第一个数组中的"quantity“，并忽略该行"reference”字段中的以下2个数组。

是否有一种方法可以将第一行的“引用”字段的所有3个数组的“数量”之和？

Answer 1

而不是在哪里，使用连接..。关于加法组BY和SUM() AS

UPDATE T t2
  JOIN (SELECT sales, SUM(quant) AS qu FROM T, JSON_TABLE(T.reference,
            "$[*]" COLUMNS(
                quant VARCHAR(400) PATH "$.quantity"
                )
            ) AS jt1
        GROUP BY sales) t1
  ON t2.sales = t1.sales
SET t2.quantity = t1.qu;

大小提琴