title: How to sum values within a field with Arrays in JSON with MYSQL?
body: <p>I have a table with a field that sometimes has multiple arrays in JSON.</p>
  
  <p>MYSQL 8.0.11</p>
  
  <p>DLL</p>
  
  <pre><code>CREATE TABLE T
      (`user` varchar(4), `sales` int, `reference` varchar(400) DEFAULT NULL, `quantity` float DEFAULT NULL, `orders` int)
  ;
  </code></pre>
  
  <p>Data</p>
  
  <pre><code>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)
  ;
  </code></pre>
  
  <p>The following query updates field "quantity" with the value of "quantity" in the "reference" field JSON array(s):</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>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.</p>
  
  <p>Is there a way to sum the "quantity" of all 3 arrays of "reference" field of the first row?</p>