data set to json with group

Question

I am working on an angular + nodejs application and I need some data from sql in json format. I am able to get the data in json but I need the data to be grouped in a tree like manner. Is there a way to do this in sql, server or in front end level?

Data I have in table

---

What I currently get

{"":[
    "3",
    "6",
    "12"
 ],
"":[
    "3",
    "6",
    "13"
  ],
"":  [
    "3",
    "7",
    "16"
  ],
"":  [
    "3",
    "7",
    "17"
  ],
"":  [
    "3",
    "8",
    "18"
  ],
"":  [
    "3",
    "8",
    "19"
  ],
"":  [
    "3",
    "9",
    "20"
  ],
"":  [
    "3",
    "9",
    "21"
  ]
 }

---

What I want in json:

{"" :{ "3": {"6": ["12", "13"], "7": ["16", "17"], "8": ["18", "19"], "9": [ "20", "21" ]} }}

Please suggest a way to do this.

Thanks in advance!

Answer 1

First, you have to get your data as valid json like below:

var items = [
    {
        l0: "3",
        l1: "6",
        l2: "12"
    },
    {
        l0: "3",
        l1: "6",
        l2: "13"
    },
    {
        l0: "3",
        l1: "7",
        l2: "16"
    },
    {
        l0: "3",
        l1: "7",
        l2: "17"
    },
    {
        l0: "3",
        l1: "8",
        l2: "18"
    },
    {
        l0: "3",
        l1: "8",
        l2: "19"
    },
    {
        l0: "3",
        l1: "9",
        l2: "20"
    },
    {
        l0: "3",
        l1: "9",
        l2: "21"
    }
  ];

Then you can get what you want by:

var result = {};
items.forEach(item => {
    result[item.l0] = result[item.l0] || {};
    result[item.l0][item.l1] = result[item.l0][item.l1] || [];
    result[item.l0][item.l1].push(item.l2); 
});

console.log(result);

Final result:

Answer 2

You can sort your SQL query results by multiple columns, this should return the data in the order you are looking for:

<original_query> + ORDER BY c1, c2, c3