How to store and retrieve JSON data from a MySQL DB in JavaScript?

Question

My Setup:

What I'm doing is pulling a very large table from MySQL (40,000+ Rows)

SpoolInfo.request.query("SELECT SpoolNumber AS a, DrawingNumber AS b, LineSize AS c, PipeSpec AS d, CU AS e, SheetNumber AS f, FluidCode AS g FROM Job" + JobNumber + ".SpoolInfo ORDER BY SpoolNumber ASC");

Doing a simple operation to it to format it how it is needed for the application I have to send it to:

if(SpoolInfo.response.data){
  SpoolInfoRowsObj = {"Cache":[]};
  SpoolInfo.response.data.forEach(function(d,di){
    SpoolInfoRowsObj.Cache.push(_.values(SpoolInfo.response.data[di]));
  });

Then storing it back into a Cache table for quicker access:
(This is needed because I can't find a way to make the .forEach loop to run faster, and currently takes over 20 seconds.)

UpdateCacheTable1.request = new AP.MySQL.Request();
UpdateCacheTable1.request.execute("UPDATE `Job" + JobNumber + "`.`CacheTable_SpoolInfo` SET `SpoolInfoObj` = '" + JSON.stringify(SpoolInfoRowsObj.Cache) + "' WHERE ID = 1");

The Problem:

When I retrieve that data back later:

CacheSpoolInfo.request.query("SELECT SpoolInfoObj FROM Job" + GetJobNumber.response.data[0].JobNumber + ".CacheTable_SpoolInfo ");
CommRef_.SpoolInfo = CacheSpoolInfo.response.data

And try to use is like this:

....
        }else if (t == "SpoolInfo"){
          rowsObj = {"Rows":[]};
          rowsObj = {"Rows":JSON.parse(CommRef_.SpoolInfo[0].SpoolInfoObj)};
        }else{
....

It has a bunch of extra "\" stuff in it for all the special characters like:

      "Rows": [
        [
          "[[\" 1-AII22-84042S01 \",\"1040\r\"],[\"0-A102-27564S01 \",\"110\r\"],.....
        ]
      ]

So of course the ouput is not the same JSON structured format that I saved.

I kinda thought using JSON.stringify() and JSON.parse() as I did would handle this.

My Question:

How can I handle this so the data I pull back can be handled the same way it would have been before I sent it to the DB?? AKA get rid of all the little slashes so JSON can parse it again.

Answer 1

The problem is that MySQL is adding the escapes when you save the string back in, because MySQL has no idea what JSON is and is storing it as a VARCHAR. An easy way to get around this is to do the escaping yourself before you store the string in the database.

For example, if you have a PHP backend, to store you would do

$escapedString = mysqli_escape_string($myJSONString);
// Store $escapedString in db

For, retrieval, you would then do

$escapedString = // query code
$myJSONString = stripslashes($escapedString);

How you handle the escape will vary based on your backend architecture. You might also consider using a database that has native support for JSON.

Another option would be to write a JavaScript function that does the stripping for you (there is no native function that does this). It should be doable with some regex. To see how you might go about this, see this existing SO question related to stripping slashes. Note that you'll need to change that a bit, since you only want to remove escape slashes while in that post the OP wants to remove all slashes (which is wrong for JSON since your data might actually contain slashes).