mysql - SQL multiply row by each element in an array

Question

I have some items in mysql table and I want to multiply the calories field from each row by each element in an array e.g for array 1,2,2, the first row is multiplied by 1, second row is multiplied by 2, third row is multiplied by 2.

Example table:

id | name | calories 
---+------+---------
 1   rice     10
 2   bread    20
 3   apple    15

So the output would become:

id | name | calories 
---+------+---------
 1   rice     10
 2   bread    40
 3   apple    30

I then want to get the total sum of the calories. How should I write the SQL statement in node.js? req.body.amount is the array of values that I want to multiply each row of calories by.

let sum = [req.body.amount, req.body.name];
let sqlquery = "SELECT SUM(? * calories) AS calories FROM food_item WHERE name IN (?)";

question from:https://stackoverflow.com/questions/65877708/sql-multiply-row-by-each-element-in-an-array

Answer 1

Use the table's id as index for the array.

SET @row_number = 0; 
Select t.* , t.calories * factors.factor result
from (
 SELECT 
    (@row_number:=@row_number + 1) AS num,
    T1.*
    from test T1) t
 join (
  select f.* from (
  Select 1 as id, 1 as factor
  union Select 2, 2
  Union Select 3,2
  ) f ) factors
   on t.num = factors.id
    Order by t.id;

see db fiddle

Using a json array:

SET @row_number = -1;
Select t.* ,
json_extract('{"R": [1,2,2]}' ,
             Concat('$.R[',t.num,']') ) jf,
t.calories * 
json_extract('{"R": [1,2,2]}' ,
             Concat('$.R[',t.num,']') ) result
from (
 SELECT 
    (@row_number:=@row_number + 1) AS num,
    T1.*
    from test T1) t
order by t.id;

Db fiddle