0

I have a MySQL table with the structure below

+------------------+------------------+----------------+
|      comp_id     |       name       |     parent     |
|------------------|------------------|----------------+
|         1        |       comp1      |      NULL      |
+------------------+------------------+----------------+
|         2        |       comp2      |       1        |
+------------------+------------------+----------------+
|         3        |       comp3      |       2        |
+------------------+------------------+----------------+
|         4        |       comp4      |       2        |
+------------------+------------------+----------------+

Assuming that no data has been inserted into the table. In other words, assuming that the table is empty how should i go about the following:

  1. traverse the JSON data below for entry into the table:
{
  "org_name":"paradise island",
  "daughters" : [
    {
      "org_name": "banana tree",
      "daughters": [
        {"org_name":"Yellow Banana"},
        {"org_name":"Brown Banana"}
      ]
    },
    {
      "org_name": "big banana tree",
      "daughters": [
        {"org_name":"green banana"},
        {"org_name":"yellow banana"},
        {
          "org_name": "Black banana",
          "daughters": [
            {"org_name": "red spider"}
          ]
        }
      ]
    }
  ]
}
  1. what effective SQL query can I write to insert the JSON above into MYSQL database at once.

I've researched a host of resources on adjacency list model and nested models but none has been exhaustive on how inserts should be done via JSON input

Heretic Monkey
  • 11,687
  • 7
  • 53
  • 122
twinsmaj
  • 55
  • 7
  • Do you really want to store this JSON data, or would you rather have JavaScript generate the JSON object for you? – Tim Biegeleisen Jun 11 '18 at 16:06
  • Thanks @TimBiegeleisen I actually want to store the JSON data. The JSON data will be sent over a REST endpoint – twinsmaj Jun 11 '18 at 16:08
  • Typically, from what I have seen, your UI/server would be generating this JSON, possibly on the fly, and then sending it. – Tim Biegeleisen Jun 11 '18 at 16:09
  • @TimBiegeleisen Exactly. But then, it has to be structured in flat manner for entry into the database table above. That's where I need help. – twinsmaj Jun 11 '18 at 16:12
  • @ippi changing database is not an option – twinsmaj Jun 11 '18 at 16:13
  • MySQL, at least in the more recent versions, does in fact have a JSON type, which you may use. Or, you could just store it as plain text. – Tim Biegeleisen Jun 11 '18 at 16:15
  • What technology are you using to get the data to/from MySQL? Seems like it would be possible to iterate over the object parsed from that JSON and create standard INSERT statements. There are no IDs in the JSON, so that will take a bit of doing. Please let us know what you've tried so far so we don't duplicate the effort. – Heretic Monkey Jun 11 '18 at 16:23
  • This seems pretty straight forward, noting that you're using `node.js`. Convert your JSON into a literal object with `JSON.parse()`, loop through it with `for...`, and have variable for each level which stringifes it back into JSON. Then, simply do an insert. – JM-AGMS Jun 11 '18 at 16:24
  • If using uuidv4's as id's would be ok for you, you could just normalize your object with a recursive function, add uuid's as id's and then do one bulk insert. – Maurice Döpke Jun 11 '18 at 16:26
  • @MikeMcCaughan I'm using nodes and sequelize ORM. – twinsmaj Jun 11 '18 at 16:31
  • @JM-AGMS I've done this but i'm a bit skeptical about performance – twinsmaj Jun 11 '18 at 16:32
  • @MauriceDöpke I haven't tried this, but won't the UUIDs get duplicated? – twinsmaj Jun 11 '18 at 16:35
  • @twinsmaj If you're just looking to optimize your code, then this is the wrong site. Go to Code Review: https://codereview.stackexchange.com/ for that. – JM-AGMS Jun 11 '18 at 16:44

1 Answers1

0

If using uuidv4's as id's would be ok for you, you could just normalize your object with a recursive function, add uuid's as id's and construct the parental relationships. After That you just bulk insert your data with whatever database client you are using.

This is an ideal use case for UUIDV4's. The chance of a collision is very unlikely, so you can consider it production safe.

You just have to make sure, that your uuid generator is random enough. Bad implementations could lead to much higher probabillities of collisions.

I suggest using the uuid package. You can look up it's sources on github. It uses nodes crypto library as random data generator which is cryptographically strong according to the documentation

Nice to know: MongoDB's ObjectId's are created in a similar way. They also don't provide 100 percent security against collision. But they are so unlikely that they consider this chance irrelevant.

EDIT: I assumed your code runs server side in node.js. Generating uuid's in the browser is not safe, because the crypto api is still experimental and users might try to intentionally cause collisions.

Maurice Döpke
  • 367
  • 3
  • 9