Change json into tree data by javascript

Question

I have this kind of data.

[
    {
        "dbname": "db1",
        "tblname": "tbl1",
        "colname": "ID_1",
        "RowCounts": "50"
    },    
    {
        "dbname": "db1",
        "tblname": "tbl1",
        "colname": "ID_2",
        "RowCounts": "50"
    },  {
    "dbname": "db1",
    "tblname": "tbl1",
    "colname": "ID_3",
    "RowCounts": "50"
  },
    {
    "dbname": "db1",
    "tblname": "tbl1",
    "colname": "ID_4",
    "RowCounts": "30"
  },{
    "dbname": "db1",
    "tblname": "tbl2",
    "colname": "ID_1",
    "RowCounts": "20"
  },{
    "dbname": "db1",
    "tblname": "tbl2",
    "colname": "ID_2",
    "RowCounts": "30"
  },{
    "dbname": "db1",
    "tblname": "tbl2",
    "colname": "ID_3",
    "RowCounts": "10"
  },{
    "dbname": "db1",
    "tblname": "tbl2",
    "colname": "ID_4",
    "RowCounts": "60"
  },{
    "dbname": "db1",
    "tblname": "tbl2",
    "colname": "ID_5",
    "RowCounts": "30"
  },{
    "dbname": "db1",
    "tblname": "tbl3",
    "colname": "ID_6",
    "RowCounts": "20"
  }
]

And I want to convert into this format.

["name":"db1", 
"rowcount":500, //sum of child row counts
"children":[
{"name":"tbl1",
"rowcount":200, //sum of children row counts
"children":[{
"name":"ID_1",
"rowcount":50
},{
"name":"ID_2",
"rowcount":50
}]},
{"name":"tbl2",
"rowcount":200,
"children":[{
}]}]}]

How can I do this? I tried this but not works.

I import csv file but its structure is not working well with tree structure so I need to convert it. It has 3 fields, dbname, tblname and colname and row counts. Just think rowcounts as value or something else. Just needed to be merged.

function createTreeData(structure) {
        const node = (name, parent = null, row_cnt = 0) => ({
            name,
            parent,
            row_cnt,
            children: []
        });
        const addNode = (parent, child) => (parent.children.push(child), child);
        const findNamed = (name, parent) => {
            for (const child of parent.children) {
                if (child.name === name) {
                    return child
                }
                const found = findNamed(name, child);
                if (found) {
                    return found
                }
            }
        }
        const TOP_NAME = "Top",
            top = node(TOP_NAME);
        for (const children of structure) {
            let par = top;
            for (const name of children) {
                const found = findNamed(name, par);
                par = found ? found : addNode(par, node(name, par.name));
            }
        }                      
        return top;
    }

It creates tree structure but not row counts and sum its children's row count.

Any help would be appreciated.

Answer 1

You could take an array of keys for nesting structure, reduce this structure and add a new object to the most inner array. On the way add rowcount.

var data = [{ dbname: "db1", tblname: "tbl1", colname: "ID_1", RowCounts: "50" }, { dbname: "db1", tblname: "tbl1", colname: "ID_2", RowCounts: "50" }, { dbname: "db1", tblname: "tbl1", colname: "ID_3", RowCounts: "50" }, { dbname: "db1", tblname: "tbl1", colname: "ID_4", RowCounts: "30" }, { dbname: "db1", tblname: "tbl2", colname: "ID_1", RowCounts: "20" }, { dbname: "db1", tblname: "tbl2", colname: "ID_2", RowCounts: "30" }, { dbname: "db1", tblname: "tbl2", colname: "ID_3", RowCounts: "10" }, { dbname: "db1", tblname: "tbl2", colname: "ID_4", RowCounts: "60" }, { dbname: "db1", tblname: "tbl2", colname: "ID_5", RowCounts: "30" }, { dbname: "db1", tblname: "tbl3", colname: "ID_6", RowCounts: "20" }],
    keys = ['dbname', 'tblname', 'colname'],
    result = [];

data.forEach(o => keys.reduce((p, k) => {
    var temp = (p.children = p.children || []).find(({ name }) => name === o[k]);
    if (!temp) p.children.push(temp = { name: o[k], rowcount: 0 });
    temp.rowcount += +o.RowCounts;
    return temp;
}, { children: result }));

console.log(result);

.as-console-wrapper { max-height: 100% !important; top: 0; }

For adding rowcount only at the leaves, you could take the old approach and reduce only until the last nesting level and add the object with count to the final array.

You could take an array of keys for nesting structure, reduce this structure and add a new object to the most inner array. On the way add rowcount.

var data = [{ dbname: "db1", tblname: "tbl1", colname: "ID_1", RowCounts: "50" }, { dbname: "db1", tblname: "tbl1", colname: "ID_2", RowCounts: "50" }, { dbname: "db1", tblname: "tbl1", colname: "ID_3", RowCounts: "50" }, { dbname: "db1", tblname: "tbl1", colname: "ID_4", RowCounts: "30" }, { dbname: "db1", tblname: "tbl2", colname: "ID_1", RowCounts: "20" }, { dbname: "db1", tblname: "tbl2", colname: "ID_2", RowCounts: "30" }, { dbname: "db1", tblname: "tbl2", colname: "ID_3", RowCounts: "10" }, { dbname: "db1", tblname: "tbl2", colname: "ID_4", RowCounts: "60" }, { dbname: "db1", tblname: "tbl2", colname: "ID_5", RowCounts: "30" }, { dbname: "db1", tblname: "tbl3", colname: "ID_6", RowCounts: "20" }],
    keys = ['dbname', 'tblname'],
    result = data.reduce((r, o) => {
        keys
            .reduce((array, k) => {
                var temp = array.find(({ name }) => name === o[k]);
                if (!temp) array.push(temp = { name: o[k], children: [] });
                return temp.children;
            }, r)
            .push({ name: o.colname, rowcount: +o.RowCounts });
        return r;
    }, []);

console.log(result);

.as-console-wrapper { max-height: 100% !important; top: 0; }