Build a TreeView nav from a flat table

Question

I have a table that looks like this:

Label1       label2              label3    
Prod         server1              azure                  
Prod         server2              azure                  
Prod         server3              azure                 
Prod         server10             azure                  
Prod         server11             aws                    
Prod         server4              aws                    
Prod         server5              azure                                  
Test         server10             aws                    
Test         server1              aws                    
Test         server12             aws   

Is there a way to return the data in such a structure that I can build a Tree Navigation menu like such: I'm using an WebAPI built in C# using EF to connect to the data which works, but there a way to return the data as such in JSON?

Prod  
                Azure  
                                Server1  
                                Server2  
                                Server3  
                AWS  
                                Server11  
                                Server4  
Test  
                AWS  
                                Sersver10  
                                Server1  
                                Server12  
  
  

Answer 1

If your data is discreet & in the particular format above then you could use GroupBy / ToDictionary which serialises well with System.Text.Json out of the box:

   using System.Text.Json;  
     
   string[][] tableRows = new[] {  
   	new [] { "Prod", "server1", "azure" },  
   	new [] { "Prod", "server2", "azure" },  
   	new [] { "Prod", "server3", "azure" },  
   	new [] { "Prod", "server10", "azure" },  
   	new [] { "Prod", "server11", "aws" },  
   	new [] { "Prod", "server4", "aws" },  
   	new [] { "Prod", "server5", "azure" },  
   	new [] { "Test", "server10", "aws" },  
   	new [] { "Test", "server1", "aws" },  
   	new [] { "Test", "server12", "aws" }  
   };  
     
     
   var results = tableRows.GroupBy(r => r[0]) /* 0 = Environment */  
   	.ToDictionary(g => g.Key,  
   		r => r.GroupBy(r => r[2]) /* 2 = Cloud provider */  
   			.ToDictionary(g => g.Key,  
   				g => g.Select(r => r[1]) /* 1 = Server name */  
   			)  
   	);  
     
   string json = JsonSerializer.Serialize(results, new JsonSerializerOptions { WriteIndented = true });  
     
   Console.WriteLine(json);  

Produces:

   {  
     "Prod": {  
       "azure": [  
         "server1",  
         "server2",  
         "server3",  
         "server10",  
         "server5"  
       ],  
       "aws": [  
         "server11",  
         "server4"  
       ]  
     },  
     "Test": {  
       "aws": [  
         "server10",  
         "server1",  
         "server12"  
       ]  
     }  
   }  

Answer 2

When I do that, I get an error from my WebAPI

InvalidOperationxception: Client side GroupBy is not supported