Inserting a nested JSON Object into MySQL

Question

I have a JSON that outputs like this in Node.js when running Console.log(myjsonobject):

{
"results":
[
    "id": 'xxxxxx',
    "size": 'xxxxx',
    "data":
        [ {             
            "date": 'xxxxx',
            "age": 'xxxx',
            "grade": 'xxxxxx'       
          },

          {             
            "date": 'xxxxx',
            "age": 'xxxx',
            "grade": 'xxxxxx'       
          }
          .... // many more data again

        ]
]   
"total": 'xxxxxxx',
"group": 'xxxxxxx'
}

I'm new to Node.js and struggling to insert myjsonobject in MySQL database.

Answer 1

Yes, you can insert your nested object in mysql. Follow these steps:

1) You must create an table that contains a JSON column, MySQL JSON Docs

CREATE TABLE myjson (jdoc JSON);

2) Connect to mysql, I'm using the mysql driver, install it:

npm install mysql

3) Correct your code: For example,at line 3, an object array must use [{ }] and not [].

3) Properly insert data to your database:

var mysql = require('mysql');

var con = mysql.createConnection({
  host: "localhost",
  user: "youruser",
  password: "yourpassword",
  database: "yourdb"
});
//Your JSON nested object;
var my_json_nested_object = {
  "results":
  [{ // <--
      "id": "xxxxxx",
      "size": "xxxxx",
      "data":
          [ {             
              "date": "xxxxx",
              "age": "xxxx",
              "grade": "xxxxxx"       
            },

            {             
              "date": "xxxxx",
              "age": "xxxx",
              "grade": "xxxxxx"       
            }

          ]
  }],   // <-- use {}
  "total": "xxxxxxx",
  "group": "xxxxxxx"
  };
//The JSON.stringify() method converts a JavaScript value to a JSON string
var sql  = "INSERT INTO myjson VALUES ('" + 
JSON.stringify(my_json_nested_object) + "');";

con.query(sql, function (err, results,fields) {

if (err) 
  throw err;
console.log(results); //or console.log(JSON.stringify(results));
con.end();
});