How can I insert a array of objects into a mysql database-CodePudding

I have a problem where I have an array of objects like this:

 [
  {
    department_id: '6256f8ae6f749617e8167416',
    employee_id: '6253ca0c6f749618a8d022af',
    employee_number: '1234'
  },
  {
    department_id: '6256f8ae6f749617e8167416',
    employee_id_id: '6253ca0c6f749618a8d022af',
    employee_number: '1503'
  }
]

and would like to use node js and mysql to insert it into a database so I have got this script

Department.assignEmployeetoDepartment = (employees, result) => {
  let employees_array = Object.values(rooms);

  db.query(
    `INSERT INTO department_employee (department_id, employee_id, employee_number) VALUES ?`,
    [employees_array],
    (err, res) => {
      if (err) {
        console.log("error: ", err);
        result(err, null);
        return;
      }

      console.log("success: ", res);
      result(null, res);
    }
  );
};

when I use the code above, I get

INSERT INTO department_employee (department_id, employee_id, employee_number) VALUES '[object Object]', '[object Object]'

which does not work for obvious reasons. I tried to stringify the object and I also tried to use a for loop to iterate over the employees array and it did not work as it says the headers were already sent.

How could I store the array (which can vary in length into the database? Thanks in advance

CodePudding user response：

As per mysql- Escaping query values

Nested arrays are turned into grouped lists (for bulk inserts), e.g. [['a', 'b'], ['c', 'd']] turns into ('a', 'b'), ('c', 'd')

You probably want to map the values to array, you can't pass array of JSON object to query

  db.query(
    `INSERT INTO department_employee (department_id, employee_id, employee_number) VALUES ?`,
    [employees_array.map(employee => [employee.department_id, employee.employee_id, employee.employee_number])],
    (err, res) => {
      if (err) {
        console.log("error: ", err);
        result(err, null);
        return;
      }

      console.log("success: ", res);
      result(null, res);
    }
  );
};

CodePudding user response：

You can't insert an array in a table, an array IS a table