[SOLVED] insert a json array in a mysql table

insert a json array in a mysql table

Using mysql2 I am parsing a json response from an endpoint and inserting some of its values in a mysql database. What I now want to do is to log this response in a table of my database. The json I receive is in a variable called data and I properly access it's values like data.object.account_country and so on.

data is a valid json like this (trimmed for privacy):

{
    "object": {
      "id": "in_1IYxloLuHwfLSa62Zjqz5jX5",
      "object": "invoice",
      "account_country": "IT",
    }
}

Now I want to store the entire json in a log table in mysql so I created a json column in my table and I am trying to insert the json.

My query looks like this:

con1.query("INSERT INTO webhook_log SET tipo='"+eventType+"', risposta='"+data+"', created_on=now()",function(err,result){
                        if(err) throw err;
                        console.log(`ðŸ””  Webhook logged`);
                    });

This throws the following error:

{ Error: Invalid JSON text: "Invalid value." at position 1 in value for column 'webhook_log.risposta'.

I also tried removing the single quotes around data but in this case I get:

{ 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 '[object Object], created_on=now()' at line 1

There is obviously something I am doing wrong here but I cannot spot the light on the error.

Solution

Per the comments above, the answer is to use JSON.stringify to convert a Javascript object into a JSON string.

Also use SQL query parameters. Once you get the hang of it, it's actually easier than doing string-concatenation and getting eyestrain trying to balance all the quotes.

con1.query("INSERT INTO webhook_log SET tipo=?, riposta=?, created_on=now()",
  [eventType, JSON.stringify(data)],
  function(err,result){ if(err) throw err; console.log(ðŸ””  Webhook logged); });