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"
    }
  }
]

Answer 4

Ok, lets try like this. First of all as i understand your preblem: u have arrays of properties of parents and child and u neet to convert it to json object. The point is here:

public static ExpandoObject DicTobj(Dictionary<string, object> properties)
        {
            var eo = new ExpandoObject();
            var eoColl = (ICollection<KeyValuePair<string, object>>)eo;

            foreach (var childColumn in properties)
                eoColl.Add(childColumn);

            return eo;
        }

U use dynamic and ExpandoObject to convert dictionary to object

The other code is trivial: u put all your objects to one using dynamic type and serialize it.

The full code:

public static Child Child1 { get; set; } = new Child
        {
            Name = "Child1"
        };

        public static Parent Parent1 { get; set; } = new Parent
        {
            Name = "Parent1"
        };

        public static Parent Parent2 { get; set; } = new Parent
        {
            Name = "Parent2"
        };

        private static void Main(string[] args)
        {
            var result = GetData(Child1, new List<Parent> {Parent1, Parent2});
            Console.WriteLine(result);
        }

        /// <summary>
        ///     This is the magic: convert dictionary of properties to object with preperties
        /// </summary>
        public static ExpandoObject DicTobj(Dictionary<string, object> properties)
        {
            var eo = new ExpandoObject();
            var eoColl = (ICollection<KeyValuePair<string, object>>) eo;

            foreach (var childColumn in properties)
                eoColl.Add(childColumn);

            return eo;
        }

        public static string GetData(Child model, List<Parent> parents)
        {
            var childColumns = GetColumns(model);
            dynamic child = DicTobj(childColumns);

            var parentsList = new List<object>();
            foreach (var parent in parents)
            {
                var parentColumns = GetColumns(parent);
                var parentObj = DicTobj(parentColumns);
                parentsList.Add(parentObj);
            }

            child.Parents = parentsList;

            return JsonConvert.SerializeObject(child);
        }


        /// <summary>
        ///     this is STUB method for example
        ///     I change return type from string[] to Dictionary[columnName,ColumnValue], becouse u need not only column names, but
        ///     with it values, i gues. If not, look commented example at the end of this method
        /// </summary>
        public static Dictionary<string, object> GetColumns(object model)
        {
            var result = new Dictionary<string, object>();
            if (model == Child1)
            {
                result.Add("Id", "1");
                result.Add("Name", "Child1");
                result.Add("Location", "SomeLocation");
            }

            if (model == Parent1)
            {
                result.Add("Id", "2");
                result.Add("Name", "Parent1");
                result.Add("SomeProperty1", "SomeValue1");
            }

            if (model == Parent2)
            {
                result.Add("Id", "3");
                result.Add("Name", "Parent1");
                result.Add("SomeProperty3", "SomeValue2");
            }

            //if u have only columNames and dont need values u can do like this
            //var columns = new[] {"Id", "Name", "SomeProperty1"};//this u get from DB
            //return columns.ToDictionary(c => c, c => new object());

            return result;
        }
    }

    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
    }

And result output:

{
  "Id": "1",
  "Name": "Child1",
  "Location": "SomeLocation",
  "Parents": [
    {
      "Id": "2",
      "Name": "Parent1",
      "SomeProperty1": "SomeValue1"
    },
    {
      "Id": "3",
      "Name": "Parent1",
      "SomeProperty3": "SomeValue2"
    }
  ]
}

Answer 5

You can pass any kind of object even if you don't have a fixed structure:

Newtonsoft.Json.JsonConvert.SerializeObject(new yourCustomObject)

By using this.

Answer 6

The best way to to get this result
- You have to create a new class which has the relation of all the class. Then use the Newtonsoft.Json.JsonConvert.SerializeObject(new organization )
Let creates a new class named organization . Add the relation which you want to see in Json. Then convert into the JSON using JsonConvert.
Or you can use the following dynamic loop

//here your database<br/>
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<br/>
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);