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

i am able to grab one value from frequency array like this ``` let inserts = [ event.unitID, event.timestamp, event.frequency[0] ]; ``` — Aravind Reddy
– Aravind Reddy, Commented Feb 5, 2020 at 16:13
It depends on what do you want to do. One solution could be creating a table apart for all of the frequencies in your array, then create a relation between that new table and your 'device_data'. Another solution could be mapping all of your readings to have the same device_id, timestamp and only change the frequency. — malarres
– malarres, Commented Feb 5, 2020 at 16:17
@malarres yes i am trying to implement the second solution(mapping all of your readings to have the same device_id, timestamp and only change the frequency) Any hints ? Thanks — Aravind Reddy
– Aravind Reddy, Commented Feb 5, 2020 at 16:21
yes the handler is the method in lambda function that processes events. — Aravind Reddy
– Aravind Reddy, Commented Feb 5, 2020 at 16:27

malarres · Accepted Answer · 2020-02-05 16:39:39Z

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
[...]

edited Feb 5, 2020 at 16:39

answered Feb 5, 2020 at 16:22

malarres

2,9481 gold badge24 silver badges37 bronze badges

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

1 Comment

malarres Over a year ago

I'm not a sql expert but IIRC you could repeat the VALUES part several times mysqltutorial.org/mysql-insert-multiple-rows

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related