CODEWITHSUNDEEP
mysqlnode.jsjson
Sam
Sam

Reputation: 41

How to insert json array object in mysql diffrent columns using node js

I have these values from json file "histogram":{ "1":5708, "2":838, "3":1097, "4":2964, "5":26308 } i wanted to insert them in mysql in column A,B,C,D,E respectively along with other data too i.e Date in node.js. on console i got the output right as i parse and stringify json. but it gives error "Column count doesn't match value count at row 1". Any help will be highly appreciated. NOTE: I am new to node so my code wont be good enough.

var dbCon = require('./db'); 
////////// necessary node modules////////

var readline = require('readline');
var stream = require('stream');
const utf8 = require('utf8');
///////////creating file stream to read a file/////////
var fs = require('fs');
var data1 = '';

// Create a readable stream
var request = require("request");
var readerStream = fs.createReadStream('app.json');

// Set the encoding to be utf8. 
 readerStream.setEncoding('UTF8');

 var outstream = new stream();
 //createInterface - read through the stream line by line and print out data from it
 var r1 = readline.createInterface(readerStream, outstream);
 var lineCount = 0;

r1.on('line', function (line) {
 // increment line count
 lineCount++;
 data1 = JSON.parse(line);
 saveRecord(data1);
 });


 function saveRecord(data1) { 
 var histogram=data1["histogram"] ; 
 var id=data1["id"] ; 
 var datetime = new Date();
 var histogramstring= JSON.stringify(histogram) ;
 var sqlq1 = "INSERT INTO wp_histo1(`id`,`A`,`B`,`C`,`D`,`E`,`Date`) VALUES (?,?,?)"  ;
 dbCon.query(sqlq1,[ id,histogramstring,datetime]);


  console.log('data inserted');
  }


   console.log("Program Ended");

Here is my console output.

code: 'ER_WRONG_VALUE_COUNT_ON_ROW',
 errno: 1136,
 sqlMessage: "Column count doesn't match value count at row 1",
 sqlState: '21S01',
 index: 0,
 sql: 'INSERT INTO wp_histo1(`id`,`A`,`B`,`C`,`D`,`E`,`Date`) VALUES 
 (\'03125\',\'{\\"1\\":5708,\\"2\\":838,\\"3\\":1097,\\"4\\":2964,\\"5\\":26308}\',\'2021-02-02 15:45:25.326\')'
}

On the other hand if i try this query

  var sqlq1 = "INSERT INTO wp_histo1(`id`,`A`,`Date`) VALUES (?,?,?)"  ;
  dbCon.query(sqlq1,[ id,histogramstring,datetime]);

Full histogram array get inserted in to column A. But i wanted to split it in A,B,C,D,E column.

Upvotes: 1

Views: 239

Answers (1)

SenorCardgage
SenorCardgage

Reputation: 194

The sqlMessage: "Column count doesn't match value count at row 1" is telling you what your first problem is. You need to specify the number of question marks in (?,?,?) to be the same number of question marks as the number of columns you are inserting data into. Which in this case is 7.

As Dave Stokes said you don't really need to place json in separate columns if you just make one column into a JSON data type column. Read https://dev.mysql.com/doc/refman/8.0/en/json.html

However, if you really need to have json values each separated into their own columns, then you can do so by altering your code:

function saveRecord(data1) {  
 let id = data1["id"]; 
 let one = data1[1];
 let two = data1[2];
 let three = data1[3];
 let four = data1[4];
 let five = data1[5];
 let datetime = new Date(); 
 let sqlq1 = "INSERT INTO wp_histo1(`id`,`A`,`B`,`C`,`D`,`E`,`Date`) VALUES (?,?,?,?,?,?,?)"  ;
 dbCon.query(sqlq1,[id,one,two,three,four,five,datetime]);

Upvotes: 1

Related Questions