insert json object with array inside into mysql table using node JS

Question

I am using aws as my backend and i have few aws lambda functions (written in node JS) that are used to insert incoming json data to amazon RDS(mysql) DB. Below is my node js code

var mysql = require('mysql');
var config = require('./config.json');
var pool = mysql.createPool({.../});

exports.handler = (event, context, callback) => {
  let inserts = [event.unitID, event.timestamp, event.frequency];
  pool.getConnection(function(error, connection) {
    connection.query({
      sql: 'INSERT INTO device_data (device_id, timestamp, frequency) VALUES (?, ?, ?);',
      timeout: 40000, 
      values: inserts
    }, function(error, results, fields) {
      connection.release();
      if (error) callback(error);
      else callback(null, results);
    });
  });
};

This is the incoming json data

"unitID": "arena-MXHGMYzBBP5F6jztnLUdCL",
  "timestamp": 1580915318000,
  "version": "1.0.0",
  "frequency": [
    60.0033,
    60.004,
    60.0044,
    60.0032,
    60.005,
    60.005,
    60.0026,
    60.0035,
    60.0036,
    60.0053
  ]
}

my frequency has array of values and i am unable to handle that to insert into DB. Any suggestions. Thanks

Answer 1

if your data is in a variable called json:

console.log(json.frequency.map( (freq) =>[json.unitID,json.timestamp,freq] ))

you can then tweak this to fit your sql to a string that replaces VALUES (?,?,?) with your desired output. e.g.:

const values = json.frequency.map( (freq) => [json.unitID,json.timestamp,freq] );
const sqlString = `'INSERT INTO device_data (device_id, timestamp, frequency) VALUES ${values.map( (row) => `(${row[0]},${row[1]},${row[2]})` ).join(',')}`

and in your code:

connection.query({
      sql:sqlString
[...]