Building json from tabular hierarchical data

Question

Let's say I have some data as you see below:

{
    "Menu": {
        "aaa": "aaa",
        "bbb": {
             "ccc": "ccc",
             "ddd": "ddd"
        },
        "eee": "eee"
     }
}

I can save this type of hierarchical data to database in a relational way like that:

https://i.stack.imgur.com/lmuq1.jpg

Sample list:

    List<MenuItem> menuItems = new List<MenuItem>();
    menuItems.Add(new MenuItem() { SiteMenuId = 1, ParentId = null, MenuName = "Menu", Url = null, SiteId = 1 });
    menuItems.Add(new MenuItem() { SiteMenuId = 2, ParentId = 1, MenuName = "aaa", Url = "aaa", SiteId = 1 });
    menuItems.Add(new MenuItem() { SiteMenuId = 3, ParentId = 1, MenuName = "bbb", Url = null, SiteId = 1 });
    menuItems.Add(new MenuItem() { SiteMenuId = 4, ParentId = 3, MenuName = "ccc", Url = "ccc", SiteId = 1 });
    menuItems.Add(new MenuItem() { SiteMenuId = 5, ParentId = 3, MenuName = "ddd", Url = "ddd", SiteId = 1 });
    menuItems.Add(new MenuItem() { SiteMenuId = 6, ParentId = 1, MenuName = "eee", Url = "eee", SiteId = 1 });

So when I get the relational data from db as a List of MenuItem objects, how can I tranlate it back to json?

public partial class MenuItem
{
    public int SiteMenuId { get; set; }
    public int SiteId { get; set; }
    public string MenuName { get; set; }
    public string Url { get; set; }
    public Nullable<int> ParentId { get; set; }
    public int CreatedUser { get; set; }
    public System.DateTime CreatedDate { get; set; }
    public Nullable<int> ModifiedUser { get; set; }
    public Nullable<System.DateTime> ModifiedDate { get; set; }
}

Do I have to use Dictionary or ExpandoObject or something? I want to have the the exact same format as I have at the begining.

Look up newtonsoft - http://www.newtonsoft.com/json and how to serialize to json. — Will, Aug 20 '15 at 14:55
Thanks Will I know about newtonsoft. But I want my json to be as same as the first format. This is what I ask actually. — xkcd, Aug 20 '15 at 15:06
I generally use Automapper to convert between the model being exposed over http and the dto. — Will, Aug 20 '15 at 15:08
Have you looked at [Build JSON Hierarchy from Structured Data](http://stackoverflow.com/q/19256579/10263) — Brian Rogers, Aug 22 '15 at 21:36
@BrianRogers Yes, but I don't want to have children:[] attribute in my json format. I need to have the exact same format as explained in my question. — xkcd, Aug 22 '15 at 21:43

Lajos Arpad · Answer 1 · 2015-08-20T18:01:25.503

You can create KeyValuePair object for that purpose:

KeyValuePair<string, List<Object>> toExport = new KeyValuePair<int, int>("Menu", new List<Object>());

Then, you can add elements, like this:

toExport.Value.Add(new KeyValuePair<string, string>("aaa", "aaa"));

To add composite things to this, you can do something like that:

KeyValuePair<string, List<Object>> bbb = new KeyValuePair<string, List<Object>>("bbb", new List<Object>());
bbb.Value.Add(new KeyValuePair<string, string>("ccc", "ccc"));
bbb.Value.Add(new KeyValuePair<string, string>("ddd", "ddd"));
toExport.Value.Add(bbb);

When you have built your object, you can use NewtonSoft's JsonConvert.SerializeObject method.

You can also create a helper class to help you.

EDIT: Creation of dynamic data.

public class DynamicKeyValueBuilder {

    private KeyValuePair<string, List<Object>> toExport;

    public DynamicKeyValueBuilder(string mainKey) {
        toExport = new KeyValuePair<string, List<Object>>(mainKey, new List<Object>());
    }

    public string getJSON() {
        return JsonConvert.SerializeObject(this.toExport);
    }

    private KeyValuePair<string, List<Object>> searchParent(List<string> path) {
        KeyValuePair<string, List<Object>> temp = (KeyValuePair<string, List<Object>>)this.toExport;
        int index = 0;
        while (index < path.Count) {
            try {
                temp = (KeyValuePair<string, List<Object>>)temp.First(item => item.Key == path.ElementAt(index)); //throws exception if value is not list or the element was not found
                index++;
            } catch (Exception exception) {
                //handle exceptions
                return null;
            }
        }
        return temp;
    }

    //If value == null, we create a list
    public boolean addElement(List<string> path, string key, string value) {
        KeyValuePair<string, Object> parent = this.searchParent(path);
        //failure
        if (parent == null) {
            return false;
        }
        parent.Value.Add((value == null) ? (new KeyValuePair<string, List<Object>>(key, new List<Object>())) : (new KeyValuePair<string, string>(key, value)));
        return true;
    }

}

Code is untested, if you encounter errors, please, let me know instead of just down-voting, I believe I am putting an effort here to help.

You can instantiate the class like this:

DynamicKeyValueBuilder myBuilder = new DynamicKeyValueBuilder("Menu");

When you intend to add a new <string, string> element, you can do it like this:

myBuilder.Add(new List<string>(new string[] {"Menu"}), "aaa", "aaa");

When you intend to add a new <string, List<Object>> element, you can do it like this:

myBuilder.Add(new List<string>(new string[] {"Menu"}), "bbb", null);

When you intend to add something inside an inner list, you can do it like this:

myBuilder.Add(new List<string>(new string[] {"Menu", "bbb"}), "ccc", "ccc");

Thanks, ite seems that KeyValuePair objects will help me. But my main question is how can I build the appropriate object using the dynamic data. Think of the number of the items in json and depth of them will vary. — xkcd, Aug 20 '15 at 17:17
@anilca, I have edited my answer. Code is untested, let me know if you find problems in it. — Lajos Arpad, Aug 20 '15 at 18:01
Actually I didn't understand how to use your builder. I added the sample list of items to the question. If you have the sample list how do you use it to create the json I expected? And what if I have 2 root elements, something other than "Menu" in the sample? — xkcd, Aug 20 '15 at 23:07
I believe that your problem is that you did not succeed in building the List expected as path. You have a ParentId there to help you in your quest. For instance, the parent of 5 is 3, the parent of 3 is 1 and the parent of 1 is null, so 1 is root. You can build a tree using the ParentId relation, for example. There was no mention of multiple roots initially, but you can use List for that purpose, or you can make toExport a List and slightly modify searchParent. — Lajos Arpad, Aug 21 '15 at 00:14

score 1 · Accepted Answer · answered Aug 22 '15 at 22:35

using Json.net, we can write a custom converter to generate our desired json from list of MenuItem.

NOTE: I omitted the reader part for the converter to make it concise (as its not really related to the question) but the logic would be similar to the writer part.

class MenuItemJsonConverter : JsonConverter
{

    public override bool CanConvert(Type objectType)
    {
        return objectType == typeof (MenuItemCollection) || objectType==typeof(List<MenuItem>);
    }

    public override object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)
    {
        throw new NotImplementedException();
    }

    public override void WriteJson(JsonWriter writer, object value, JsonSerializer serializer)
    {
        var map=new Dictionary<int,JObject>();
        var collection = (List<MenuItem>) value;
        var root=new JObject();

        var nestedItems=collection.GroupBy(i => i.ParentId).ToLookup(g=>g.Key); //or we can simply check for item.Url==null but I believe this approach is more flexible

        foreach (var item in collection)
        {
            if (item.ParentId == null)
            {
                var firstObj=new JObject();
                root.Add(item.MenuName,firstObj);
                map.Add(item.SiteMenuId,firstObj);
                continue;
            }

            var parent = map[item.ParentId.Value];

            if (!nestedItems.Contains(item.SiteMenuId))
            {
                parent.Add(item.MenuName,item.Url);
                continue;
            }

            var jObj = new JObject();
            parent.Add(item.MenuName, jObj);
            map.Add(item.SiteMenuId, jObj);
        }

        writer.WriteRaw(root.ToString());
    }
}

here is direct usage example:

        var menuItems = new List<MenuItem>();
        menuItems.Add(new MenuItem() { SiteMenuId = 1, ParentId = null, MenuName = "Menu", Url = null, SiteId = 1 });
        menuItems.Add(new MenuItem() { SiteMenuId = 2, ParentId = 1, MenuName = "aaa", Url = "aaa", SiteId = 1 });
        menuItems.Add(new MenuItem() { SiteMenuId = 3, ParentId = 1, MenuName = "bbb", Url = null, SiteId = 1 });
        menuItems.Add(new MenuItem() { SiteMenuId = 4, ParentId = 3, MenuName = "ccc", Url = "ccc", SiteId = 1 });
        menuItems.Add(new MenuItem() { SiteMenuId = 5, ParentId = 3, MenuName = "ddd", Url = "ddd", SiteId = 1 });
        menuItems.Add(new MenuItem() { SiteMenuId = 6, ParentId = 1, MenuName = "eee", Url = "eee", SiteId = 1 });

        var json = JsonConvert.SerializeObject(menuItems,Formatting.Indented,new MenuItemJsonConverter());

or we can derive from List<> and decorate it with [JsonConverter(typeof(MenuItemJsonConverter))] for more convenience:

[JsonConverter(typeof(MenuItemJsonConverter))]
class MenuItemCollection : List<MenuItem>
{      
}

then simply use it like:

        var menuItems = new MenuItemCollection();
        menuItems.Add(new MenuItem() { SiteMenuId = 1, ParentId = null, MenuName = "Menu", Url = null, SiteId = 1 });
        menuItems.Add(new MenuItem() { SiteMenuId = 2, ParentId = 1, MenuName = "aaa", Url = "aaa", SiteId = 1 });
        menuItems.Add(new MenuItem() { SiteMenuId = 3, ParentId = 1, MenuName = "bbb", Url = null, SiteId = 1 });
        menuItems.Add(new MenuItem() { SiteMenuId = 4, ParentId = 3, MenuName = "ccc", Url = "ccc", SiteId = 1 });
        menuItems.Add(new MenuItem() { SiteMenuId = 5, ParentId = 3, MenuName = "ddd", Url = "ddd", SiteId = 1 });
        menuItems.Add(new MenuItem() { SiteMenuId = 6, ParentId = 1, MenuName = "eee", Url = "eee", SiteId = 1 });

        var json = JsonConvert.SerializeObject(menuItems,Formatting.Indented);

score 0 · Answer 3 · answered Aug 20 '15 at 15:36

0

If you can use NewtonSoft, use to deserialize your JSON content and see how the resulting object looks like. Then, create a class that matches the resulting structure of the deserialization. Reverse engineering...

Use this method:

var obj = JsonConvert.DeserializeObject("{ "menu": { "aaa": "aaa"......} }");

Let me know your findings.

answered Aug 20 '15 at 15:36

Ivan

147
4

But my json will not be same everytime. Think of the number of the items in json and depth of them will vary – xkcd Aug 20 '15 at 17:19

Building json from tabular hierarchical data

3 Answers3