How to sum values within a field with Arrays in JSON with MYSQL?

Question

I have a table with a field that sometimes has multiple arrays in 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)
;

Data

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)
;

The following query updates field "quantity" with the value of "quantity" in the "reference" field JSON array(s):

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;

The query takes the "quantity" in the first array of the first row and ignores the following 2 arrays in that row's "reference" field.

Is there a way to sum the "quantity" of all 3 arrays of "reference" field of the first row?

BaliJack · Accepted Answer · 2018-11-14 01:16:52Z

1

Instead of WHERE, use JOIN ... ON plus GROUP BY and 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;

db-fiddle

answered Nov 14, 2018 at 1:16

BaliJack

1172 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to sum values within a field with Arrays in JSON with MYSQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related