Store JSON data in MySQL table

Question

I have a problem with storing JSON data in MySQL table using NodeJS.

JSON data looks like this:

{
    "header":
    {
        "file1":0,
        "file2":1,
        "subfiles":{
          "subfile1":"true",
          "subfile2":"true",
        }
    },

    "response":
    {
        "number":678,
        "start":0,
        "docs":[
            {
                "id":"d3f3d",
                "code":"l876s",
                "country_name":"United States",
                "city":"LA"
            },
            {
                "id":"d2f2d",
                "code":"2343g",
                "country_name":"UK",
                "city":"London"
            }
        ]
    }
}

and I want to store only fields in docs array (or response object). I'm trying to get data and store in mysql in this way:

var express = require('express');
var mysql = require('mysql');
var request = require("request");

var app = express();

app.use('/', express.static('../client/app'));
app.use('/bower_components', express.static('../client/bower_components/'));

var server = require('http').createServer(app);

var bodyParser = require('body-parser');
app.jsonParser = bodyParser.json();
app.urlencodedParser = bodyParser.urlencoded({ extended: true });

//mysql connection setup
var connection = mysql.createConnection({
    host : "localhost",
    port: "3306",
    user : "root",
    password : "root",
    database : "db",
    multipleStatements: true
});

request('http://url.com', function (error, response, body) {
  if (!error && response.statusCode == 200) {
    //console.log(body) //
  }

    var data = body.toString();

    console.log(string);
    var query = connection.query('INSERT INTO table SET ?', data, function(err, result) {
         // Finish
    });
    console.log(query.sql);
});          

server.listen(3000, function () {
    'use strict';
});

In log I got

INSERT INTO table SET '{\n  \"header\":{\n    \"file1\":0,\n    \"file2\":1,\n    \"subfiles\":{\n      \"subfile1\":\"true\",\n     \"subfile2\":\"true\"}},\n  \"response\":{\"number\":678,\"start\":0,\"docs\":[\n      {\n        \"id\":\"d3f3d\",\n        \"code\":\"l876s\",\n....

output message, but I don't have data in MySQL table. Do I need to specify every column in query?

Roland Starke · Accepted Answer · 2020-03-03T11:30:58.407

6

at your //Finish comment you should have added some console.log(err) to see why there was no data inserted.

Here the solution:

var data = JSON.parse(body);
var responseJson = JSON.stringify(data.response);

var query = connection.query('INSERT INTO table SET column=?', [responseJson], function(err, result) {
     if(err) throw err;
     console.log('data inserted');
});

edited Mar 03 '20 at 11:30

answered Jan 22 '16 at 15:47

Roland Starke

1,658
1
14
19

Ok, then I need to insert data stored in responseJson. Do I need to define all columns in sql query (I still dont have data in db)? For example `connection.query("INSERT INTO table VALUES('',?,?,?...)",[id, country_name, city....],function(err, rows, fields) ` – corry Jan 22 '16 at 15:59
1

@corry: I think you can use one of these: `query('INSERT INTO tbl_name (col) VALUES(?)', 'value')` or `query('INSERT INTO tbl_name SET col=?', 'value')` or `query('INSERT INTO tbl_name SET ?', {col: 'value'})` – Roland Starke Jan 22 '16 at 16:11
I'm trying to insert data into db [as explained here](http://stackoverflow.com/questions/8899802/how-do-i-do-a-bulk-insert-in-mysql-using-node-js?lq=1) and I got an error `Error: ER_PARSE_ERROR: 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 ''[{\"id\":\"d3f3d\",\"code\":\"' at line 1`. Query is defined: `var sql = "INSERT INTO table (id, code, country_name, city) VALUES ?";` Do you know where could be the cause of the problem? Thank you. – corry Jan 23 '16 at 14:58
But now how do you call the JSON without all the backslashes? – Stephen Kuehl Dec 05 '16 at 23:49

Store JSON data in MySQL table

1 Answers1

Linked