How to create hierarchy in json string from string array?

Question

I am trying to generate json string for the hierarchy like below:

Company(select * from Company)
    Department(select * from Department)
        Employee(select * from Employee)

Each of the above query will return fields like below:

Company Fields -  (Id,Name,Location)
Department Fields - (Id,Name,CompanyId)
Employee Fields - (Id,Name,DepartmentId)

Now I am trying to generate JSON string for above entities like below:

Expected output:

{
  "Id": "",
  "Name": "",
  "Location": "",
  "Department": 
        {
           "Id": "",
           "Name": "",
           "CompanyId": "",
           "Employee" : 
               {
                  "Id": "",
                  "Name": "",
                  "DepartmentId": "", 
               }
        }
}

Code:

public string GetData(Child model,List<Parent> parents)
        {
           var fields = new List<string[]>();
           if (parents != null)
           {
                foreach (var parent in parents)
                {
                        var columns = GetColumns(parent); //returns string[] of columns
                        fields.Add(columns);
                 }
            }
            fields.Add(GetColumns(model));
            string json = JsonConvert.SerializeObject(fields.ToDictionary(key => key, v => string.Empty),
                                        Formatting.Indented);
            return json;
        }

Now when I don't have any parents and want to generate json string for only child then below code is working fine:

string json = JsonConvert.SerializeObject(fields.ToDictionary(key => key, v => string.Empty),Formatting.Indented)

Output :

{
  "Id": "",
  "Name": "",
  "Location": "",
}

But now I want to generate JSON for my hierarchy with any such inbuilt way.

I know I can loop,append and create json string but I want to do this in better way like I have done for my child.

Update:

public class Child
{
    public string Name { get; set; } // Contains Employee
   //Other properties and info related to process sql query and connection string
}


public class Parent
{
    public string Name { get; set; } // Contains Company,Department.
    public string SqlQuery { get; set; } // query related to Company and Department.
    //Other properties and info related to connection string
}

Answer 1

I created a class that holds the Information similarly to what you proposed, in a child-parent structure. I also added a custom little Parser that works recursively. Maybe that's what you need and/or what gives you the ideas you need to fix your problem.

I also altered the output a little bit, by adding the angled brackets ( "[ ]" ). I think that's what you will need with multiple children. At least that's what the JSON validator tells me that I posted below. If you don't need/ want them, just remove them in the parser.

I don't think you can use the parser you used in your example without having some form of extra fields like I showed in my previous answer, since those parsers usually go for property names as fields and I guess you don't want to create classes dynamically during runtime.

I also don't think that it is possible for you to create a dynamic depth of your parent-child-child-child...-relationship with Lists, Arrays or Dictionaries, because those structures have a set depth as soon as they are declared.

Class:

public class MyJsonObject
{
    public List<string> Columns = new List<string>();

    public string ChildName;
    public List<MyJsonObject> Children = new List<MyJsonObject>(); 
}

Parser:

class JsonParser
{
    public static string Parse(MyJsonObject jsonObject)
    {
        string parse = "{";

        parse += string.Join(",", jsonObject.Columns.Select(column => $"\"{column}\": \"\""));

        if (!string.IsNullOrEmpty(jsonObject.ChildName))
        {
            parse += $",\"{jsonObject.ChildName}\":";

            parse += $"[{string.Join(",", jsonObject.Children.Select(Parse))}]";
        }

        parse += "}";

        return parse;
    }
}

Usage:

class Program
{
    static void Main(string[] args)
    {
        MyJsonObject company = new MyJsonObject();
        company.ChildName = "Department";
        company.Columns.Add("Id");
        company.Columns.Add("Name");
        company.Columns.Add("Location");

        MyJsonObject department = new MyJsonObject();
        department.ChildName = "Employee";
        department.Columns.Add("Id");
        department.Columns.Add("Name");
        department.Columns.Add("CompanyId");

        MyJsonObject employee1 = new MyJsonObject();
        employee1.Columns.Add("Id");
        employee1.Columns.Add("Name");
        employee1.Columns.Add("DepartmentId");

        MyJsonObject employee2 = new MyJsonObject();
        employee2.Columns.Add("Id");
        employee2.Columns.Add("Name");
        employee2.Columns.Add("DepartmentId");

        company.Children.Add(department);
        department.Children.Add(employee1);
        department.Children.Add(employee2);

        var json = JsonParser.Parse(company);
    }
}

Output and Link to JSON-Validator:

https://jsonformatter.curiousconcept.com/

{  
   "Id":"",
   "Name":"",
   "Location":"",
   "Department":[
      {  
         "Id":"",
         "Name":"",
         "CompanyId":"",
         "Employee":[
            {  
               "Id":"",
               "Name":"",
               "DepartmentId":""
            },
            {  
               "Id":"",
               "Name":"",
               "DepartmentId":""
            }
         ]
      }
   ]
}

Answer 2

Perhaps I'm missing something. If you create the classes you need in the heirachy, instantiate them with data and then serialize them, the structure will be created for you.

using System.Web.Script.Serialization;

public class Employee 
{
   public int Id {get; set; }
   public string Name {get; set; }
   public int DepartmentId {get; set; }   
}

public class Department 
{
   public int Id {get; set; }
   public string Name {get; set; }
   public string CompanyId {get; set; }
   public List<Employee> {get; set;}
}

public class Company {
   public int Id {get; set; }
   public string Name {get; set; }
   public string Location {get; set; }
   public List<Department> {get; set;}
}

var myCompany = new Company();
// add departments and employees

var json = new JavaScriptSerializer().Serialize(myCompany);

Answer 3

You can use dynamic:

//here your database
dynamic[] company = new object[] { new { Name = "Company1", DepartmentId = 1 }, new { Name = "Company2", DepartmentId = 2 } };
dynamic[] department = new object[] { new { DepartmentId = 1, Name = "Department1" }, new { DepartmentId = 2, Name = "Department2" } };

//select from database
var data = from c in company
    join d in department on c.DepartmentId equals d.DepartmentId
    select new {Name = c.Name, Department = d};

var serialized = JsonConvert.SerializeObject(data);

result:

[
  {
    "Name": "Company1",
    "Department": {
      "DepartmentId": 1,
      "Name": "Department1"
    }
  },
  {
    "Name": "Company2",
    "Department": {
      "DepartmentId": 2,
      "Name": "Department2"
    }
  }
]