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

Question

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.

The query is bad, You are inserting three 'id','A', & 'date' but are passing id, A, B, C, D, E, & date. - that is many more than three. If you column A is JSON data type simple replace the A-B-C-D-E with A. — Dave Stokes
– Dave Stokes, Commented Feb 2, 2021 at 16:47

SenorCardgage · Accepted Answer · 2021-02-02 17:39:14Z

1

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]);

answered Feb 2, 2021 at 17:39

SenorCardgage

1946 bronze badges

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

3 Comments

Sam Over a year ago

Many thanks @Dave and @SenorCardgage. @SenorCardgage when i try to put 7 ? as you mentioned i get the following error code: 'ER_PARSE_ERROR', errno: 1064, sqlMessage: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?,?,?,?)' at line 1", sqlState: '42000', index: 0, sql: 'INSERT INTO wp_histo1(id,A,B,C,D,E,Date) VALUES (\'ru.mosparking\',\'{\\"1\\":5708,\\"2\\":838,\\"3\\":1097,\\"4\\":2964,\\"5\\":26308}\',\'2021-02-02 17:59:22.081\',?,?,?,?)'

Sam Over a year ago

Actually it worked when i try it like this function saveRecord(data1) { let id = data1["id"]; let one = data1["histogram"][1]; let two = data1["histogram"][2]; let three = data1["histogram"][3]; let four = data1["histogram"][4]; let five = data1["histogram"][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]); Many thanks for quick help.

SenorCardgage Over a year ago

Good work, I didn't realize that histogram was being used as an array holding those values for A B C D E. That makes sense now. I was wondering why you had that in your original code. I'm glad you got that figured out.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related