Insert Tree Json into Database

Question

I'm given a JSON (tree structure JSON) and I'm creating an API that will insert all the data at once into the database (organizations with relations). the organisation may have multiple parents and children.

The JSON look's something like this:

{
  "org_name": "organisation",
  "childrens": [
    {
      "org_name": "company tree",
      "childrens": [
        {
          "org_name": "company one"
        },
        {
          "org_name": "company two"
        },
        {
          "org_name": "company three"
        }
      ]
    },
    {
      "org_name": "Big organisation",
      "childrens": [
        {
          "org_name": "company one"
        },
        {
          "org_name": "company two"
        },
        {
          "org_name": "company four"
        },
        {
          "org_name": "company three",
          "childrens": [
            {
              "org_name": "smal farm"
            }
          ]
        }
      ]
    }
  ]
}

 my database schema (which I'm thinking)
 id   |   org_name   |  level  | parent

 *level - tells at which level we are since this is a tree and helps to plot relations

How do I insert these records into the database with their relations? I need to do this in javascript.

Does this answer your question? [Construct flat array from tree of objects](https://stackoverflow.com/questions/32609284/construct-flat-array-from-tree-of-objects) — James, Aug 23 '22 at 15:59
MySQL, since I'm hoping to handle well even with up to 100K relations — code_freak, Aug 23 '22 at 16:06

deep206 · Accepted Answer · 2022-08-23T16:09:24.920

const maintree = [{
  "org_name": "organisation",
  "childrens": [
    {
      "org_name": "company tree",
      "childrens": [
        {
          "org_name": "company one"
        },
        {
          "org_name": "company two"
        },
        {
          "org_name": "company three"
        }
      ]
    },
    {
      "org_name": "Big organisation",
      "childrens": [
        {
          "org_name": "company one"
        },
        {
          "org_name": "company two"
        },
        {
          "org_name": "company four"
        },
        {
          "org_name": "company three",
          "childrens": [
            {
              "org_name": "smal farm"
            }
          ]
        }
      ]
    }
  ]
}];


function recursiveBuild(tree, level, parent) {
  for(const item of tree) {
        if(item.org_name) {
        let html = document.getElementById(`test`).innerHTML;
            html += `orgname: ${item.org_name}, level: ${level}, parent: ${parent} <br/>`;
        document.getElementById(`test`).innerHTML = html;
        if(item.childrens) {
        recursiveBuild(item.childrens, level+1, item.org_name);
      } else return
    }
  }
}

recursiveBuild(maintree, 0, "");

<div id="test">
</div>

You can use a recursive function like this to insert your records depending on your database. Here I have explained using HTML and added string after each line break for your code to build a flat structure like table.

Ideally you should be lazy loading data if you have 100k records. — deep206, Aug 23 '22 at 16:10
This doesn't handle the last level, also doesn't handle the children correctly... — Pablo Zehle, Jul 20 '23 at 22:54

Insert Tree Json into Database

1 Answers1