Deserialize exported Trello JSON to C# objects

Trello JSON to C# objects

Trello is an excellent issue management system, and much more. We use it for project management and allow transparency of operations to our customers. Exporting of Trello data is possible, and the output of the data is in JSON format. While JSON is easy to understand, data analysts require the data in a format that is easier for them to understand – such as CSV, Excel, database table(s) etc. This article describes how conversion of Trello JSON to C# objects helps with formatting Trello data with C#.

I have built a prototype project to take the exported JSON file from Trello and save the data in C# objects. This process is called deserialization from JSON to C# objects. The C# objects can then be serialized (converted) to a CSV, Excel, database table(s) etc. I have only implemented deserialization in this project. Feel free to get the source code from GitHub. An advanced tool called Trello.NET is available on GitHub also.

Trello JSON to C# objects

Trello JSON

The exported file from Trello looks something like this:

{
	"id": "51cfc36960e453730c000a95",
	"name": "Trello Json Testing",
	...
	"prefs": {
		"permissionLevel": "private",
		"voting": "members",
		...
	},
	...
	"memberships": [{
		"id": "51cfc36960e453730c000a99",
		"idMember": "4f0bb09204368bb5292ffdc3",
		...
	}],
	...
	"lists": [{
		"id": "51cfc36960e453730c000a96",
		"name": "To Do",
		...
	},
	{
		"id": "51cfc36960e453730c000a97",
		"name": "Doing",
		...
	},
	...
	"cards": [{
		"id": "51cfc37a1997b8545900013f",
		...
		"checkItemStates": [{
			"idCheckItem": "51cfc3b452eca4c403000396",
			"state": "complete"
		}],		
	},
	...
	{
		"id": "51cfc37a1997b8545900013f",
		...
		"checkItemStates": [{
			"idCheckItem": "51cfc3b452eca4c403000396",
			"state": "complete"
		}],		
	}]
	...
	...

Deciphering Trello JSON

Let’s break this information into pieces for easier understanding:

{
	"id": "51cfc36960e453730c000a95",
	"name": "Trello Json Testing",
	...
}

This is the root of information. It means, is export file has property called ID, Name and other such properties. To represent this information, our object will look like this:

using System;
using System.Collections.Generic;
using System.Linq;

namespace TrelloJson
{
    class Trello
    {
        public string Id { get; set; }
        public string Name { get; set; }
        ...
    }
}

Some properties in the root can be complex. For example:

"prefs": {
		"permissionLevel": "private",
		"voting": "members",
		...
	}

Some more details

This means the root has an object called prefs with its own properties such as PermissionLevel, Voting etc. To allow deserialization of nested Trello JSON to C# objects, we create a class for pref. We also modify our root Trello class.

    class Pref
    {
        public string PermissionLevel { get; set; }
        public string Voting { get; set; }
        ...
    }

    class Trello
    {
        public string Id { get; set; }
        public string Name { get; set; }
        ...
        public Pref Prefs { get; set; }
        ...
    }

There are other objects that add more complexity. For example:

"memberships": [{
		"id": "51cfc36960e453730c000a99",
		"idMember": "4f0bb09204368bb5292ffdc3",
		...
	}],

Hierarchy of Trello JSON

This means that root has an object called Memberships, which in turn has its own properties such as ID, IdMember etc. Notice that these properties are within squared brackets [{comma separated properties}, {comma separated properties}, …]. This indicates that Membership may have multiple objects with its own properties. At this point the hierarchy looks something like this:

Root
--id
--name
--pref
---+++ pref's property 1
---+++ pref's property 2
--membership
---+++ membership object 1
---+++--- object 1's property 1
---+++--- object 1's property 2
---+++ membership object 2
---+++--- object 2's property 1
---+++--- object 2's property 2

Deserializing JSON

To deserialize Trello JSON to C# objects with this nesting and complexity, we will add a class for Membership and adjust our root Trello class appropriately.

    class Membership
    {
        public string Id { get; set; }
        public string IdMember { get; set; }
        ...
    }

   class Trello
    {
        public string Id { get; set; }
        public string Name { get; set; }
        ...
        public Pref Prefs { get; set; }
        ...
        public List<Membership> Memberships { get; set; }
    }

Notice that root Trello class accommodates multiple memberships using a List of Membership. To add to this complexity, we have cards. The root has a cards object. There can be one or more cards. Each card can have one or more properties and objects. Let’s look at the cards JSON object.

	"cards": [{
		"id": "51cfc37a1997b8545900013f",
		...
		"checkItemStates": [{
			"idCheckItem": "51cfc3b452eca4c403000396",
			"state": "complete"
		}],		
	},
	...
	{
		"id": "51cfc37a1997b8545900013f",
		...
		"checkItemStates": [{
			"idCheckItem": "51cfc3b452eca4c403000396",
			"state": "complete"
		}],		
	}]

Additional breakdown

Notice that there can be one or more cards. Each card has property ID. It also has an object called CheckItemStates. Card can have one or more CheckItemStates. Each CheckItemState has 2 properties and the hierarchy looks like this:

Root
--id
--name
...
--card
---+++ card object 1
---+++--- object 1's property 1
---+++--- object 1's badge
---+++---+++ badge's property 1
---+++---+++ badge's property 2
---+++--- object 1's checkItemStates object
---+++---+++ checkItemState object 1
---+++---+++--- property IdCheckItem
---+++---+++--- property State
---+++---+++ checkItemState object 2
---+++---+++--- property IdCheckItem
---+++---+++--- property State
...
---+++ card object 2
---+++--- object 2's property 1
---+++--- object 2's badge
---+++---+++ badge's property 1
---+++---+++ badge's property 2
---+++--- object 2's checkItemStates object
---+++---+++ checkItemState object 1
---+++---+++--- property IdCheckItem
---+++---+++--- property State
---+++---+++ checkItemState object 2
---+++---+++--- property IdCheckItem
---+++---+++--- property State

This level of JSON nesting can also be handled by building appropriate classes like so:

    class CheckItemState
    {
        public string IdCheckItem { get; set; }
        public string State { get; set; }
    }

    class Badge
    {
        public string Votes { get; set; }
        public string ViewingMemberVoted { get; set; }
    }

    class Card
    {
        public string Id { get; set; }
        public Badge Badges { get; set; }
        public List<CheckItemState> CheckItemStates { get; set; }
        ...
    }

    class Trello
    {
        public string Id { get; set; }
        public string Name { get; set; }
        ...
        public Pref Prefs { get; set; }
        ...
        public List<Membership> Memberships { get; set; }
        public List<Card> Cards { get; set; }
        ...
    }

Serialize data into objects

Great. Now we have classes set up just like the JSON is set up. We will use an object called JavaScriptSerializer() from System.Web.Script.Serialization namespace. Add a reference to System.Web.Extensions to get access to the serialization namespace. Notice how easy it is to allow JavaScriptSerializer to parse JSON to our objects.

using System;
using System.IO;
using System.Web.Script.Serialization;

namespace TrelloJson
{
    class Program
    {
        private const string JsonFile = @"trello-json-testing.json";

        static void Main()
        {
            // store all data from the given file into a data variable
            var data = File.ReadAllText(JsonFile);

            // deserialize data. After deserialization, our object json will be 
            // populated with information from JSON file
            var serializer = new JavaScriptSerializer();
            var json = serializer.Deserialize<Trello>(data);

            // write some information about our objects we just populated from
            // JSON file to do a proof of concept
            Console.WriteLine("Board {0} has {1} lists, {2} cards, {3} actions", 
                json.Name,
                json.Lists.Count,
                json.Cards.Count,
                json.Actions.Count);

            // optionally, write some data to file using our objects
            //var sb = new StringBuilder();
            //sb.AppendLine("Name|Created|Modified|Archived|List");
            //foreach (var card in json.Cards)
            //{
            //    sb.AppendLine(card.ToString() + "|" + json.ListName(card.IdList));
            //}
            //File.WriteAllText(JsonFile + ".output.txt", sb.ToString());

            Console.WriteLine("Done");
            Console.Read();

        }
    }
}

That’s all there is to serializing Trello JSON to C# objects. You could write the data from objects to a CSV file, if you like. You could store the data in database table(s), or build your own workflows.

If you would like to ingest only a few properties from JSON, you could just remove unnecessary properties from your classes and JavaScriptSerializer will completely ignore those properties in JSON to which you don’t have any mapped properties in your objects.

Hopefully this code set will help deserialize Trello JSON to C# objects (POCO) and serialize the data to a format that is easier for you to analyze. Feel absolutely free to fork/extend the examples and share on GitHub.