Insert JSON data into MySQL Database

JavaScript
1

Hi everyone,
I have a project that require to insert JSON data into MySQL database using plain javascript.
Let’s assume I have the following data and I need it to be decoded and inserted to mysql database.

{ "class": "go.GraphLinksModel", 
  "nodeDataArray": [ 
    {"key":1, "text":"Alpha Co Ltd", "colour":"white", "category":"Loading", }, 
    {"key":2,"text":"Sales", "level":"division", "colour":"skyblue"}, 
    {"key":3,"text":"Operations", "level":"division", "colour":"limegreen"}, 
    {"key":4,"text":"Onboarding", "level":"process", "colour":"skyblue"}, 
    {"key":5,"text":"Sales Reps", "level":"process", "colour":"skyblue"}, 
    {"key":6,"text":"Operations", "level":"process", "colour":"limegreen"}, 
    {"key":7,"text":"Oracle", "level":"application", "colour":"grey"}, 
    {"key":8,"text":"MS Access", "level":"application", "colour":"grey"}, 
    {"key":9,"text":"MS Access", "level":"application", "colour":"grey"}, 
    {"key":10,"text":"SAP", "level":"application", "colour":"grey"}, 
    {"key":11,"text":"Leads", "level":"dataset", "colour":"lightgrey"}, 
    {"key":12,"text":"Referrals", "level":"dataset", "colour":"lightgrey"}, 
    {"key":13,"text":"Sales Reps", "level":"dataset", "colour":"lightgrey"}, 
    {"key":14,"text":"Orders", "level":"dataset", "colour":"lightgrey"}, 
    {"key":15,"text":"Customer ID", "level":"dataelement", "colour":"white", "category":"End"}, 
    {"key":16,"text":"Customer name", "level":"dataelement", "colour":"white", "category":"End"}, 
Confidential - Rockit Solutions Limited November 2018 
    {"key":17,"text":"SalesRep ID", "level":"dataelement", "colour":"white", "category":"End"}, 
    {"key":18,"text":"SalesRep name", "level":"dataelement", "colour":"white", "category":"End"}, 
    {"key":19,"text":"SalesRep name", "level":"dataelement", "colour":"white", "category":"End"}, 
    {"key":20,"text":"Order ID", "level":"dataelement", "colour":"white", "category":"End"}, 
    {"key":21,"text":"Customer ID", "level":"dataelement", "colour":"white", "category":"End"}, 
    {"key":22,"text":"SalesRep ID", "level":"dataelement", "colour":"white", "category":"End"}
  ], 
  "linkDataArray": [
    {"from":1, "to":2}, 
    {"from":1, "to":3}, 
    {"from":2, "to":4}, 
    {"from":2, "to":5}, 
    {"from":3, "to":6}, 
    {"from":4, "to":7}, 
    {"from":4, "to":8}, 
    {"from":5, "to":9}, 
    {"from":6, "to":10}, 
    {"from":7, "to":11}, 
    {"from":8, "to":12}, 
    {"from":9, "to":13}, 
Confidential - Rockit Solutions Limited November 2018 
    {"from":10, "to":14}, 
    {"from":11, "to":15}, 
    {"from":11, "to":16}, 
    {"from":12, "to":17}, 
    {"from":12, "to":18}, 
    {"from":13, "to":19}, 
    {"from":14, "to":20}, 
    {"from":14, "to":21}, 
    {"from":14, "to":22} 
  ]
}

It requires the following 5 fields:

  1. Parent ID. Automatically generated unique ID.

  2. Child Type. The type is one of (Process, Application, Dataset or DataElement)

  3. Child Node. The node label text.

  4. Child ID. Automatically generated unique ID.

  5. GroupColour. Only applies to Nodes of type=Process.

When the script is run, parse the JSON data to identify unique IDs for every node.

Each of the four node types {Process, Application, Dataset and DataElement} should be given a unique ID prefixed by PRO, APP DSE, DEL respectively. So if there are 3 processes they would have IDs; PRO1,PRO2,PRO3. These must be continuous ID numbers and there must not be any gaps in between eg PRO1,PRO2,PRO4.

For every node identify the child name (ie the text label), type (ie Process, Application, Dataset, DataElement), child ID, parent ID and colour (eg sky-blue) if applicable.

Please, if anyone can give me some advice about haw can I achieve this.

Thank you in advance.

2

I don’t know where the “Confidential - Rockit Solutions Limited November 2018” in your example is coming from. It would be broken JSON

I have never seen any JavaScript-only code that INSERTed data into MySQL. Typically the client-side passes to a sever-side script that does the INSERT.

Anyway, the JSON looks to have some keys that relate to database fields.

  • nodeDataArray
    • key
    • text
    • level (not present in “key 1”)
    • colour
    • category (not present “key 2” to “key 14”)
  • linkDataArray
    • from
    • to

I am confused about “Parent ID” and “Child ID”. You say auto unique, but I’m guessing they should relate to the JSON in some way? i.e. the linkDataArray

“Child Type”, “Child Node”, and “GroupColour” relate to “level”, “text”, and “colour” so that should be easy enough.

3

Well given that that’s an outsourcing data aggregation company… i think the more important question is is it legal for you to be posting the data to a public internet forum?

I’m guessing he’s copy/pasting out of a doc with a footer/header.

That would be because it would be a security hole the size of the grand canyon, because the javascript would have to be able to publicly read the password to access the server. Node.js has some implementations for it, but that’s because Node code is designed to be run locally instead of on an open webpage.

Forming a MYSQL insert set wouldnt be difficult from a JSON, it would simply be a case of: (Spitballing pseudocode. Not tested.)

Foreach ObjectLevel1 As TableName => ObjectLevel2 {
  Foreach ObjectLevel2 As DeadIndex => ObjectLevel3 {
    output.append("INSERT INTO "+TableName+" ("+ObjectLevel2.keys().join(",")+") VALUES ("+ObjectLevel2.values().map(function (x) { return typeof(x) === 'string' ? '"'+x+'"' : x; }).join(",")+");";
  } 
}
2 Likes
4

Thank you for your responses.

Well given that that’s an outsourcing data aggregation company… i think the more important question is is it legal for you to be posting the data to a public internet forum?

This data is dummy data and is not related with ‘‘Rockit Solutions Limited’’.

The relations between parent and children has to be something like in the following example.
Flow Builder

5

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.