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

The exported file from Trello looks something like this:

C#

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

{

"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:

C#

1

2

3

4

5

6

7

{

"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:

C#

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

usingSystem;

usingSystem.Collections.Generic;

usingSystem.Linq;

namespaceTrelloJson

{

classTrello

{

publicstringId{get;set;}

publicstringName{get;set;}

...

}

}

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

C#

1

2

3

4

5

6

7

"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.

C#

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

classPref

{

publicstringPermissionLevel{get;set;}

publicstringVoting{get;set;}

...

}

classTrello

{

publicstringId{get;set;}

publicstringName{get;set;}

...

publicPrefPrefs{get;set;}

...

}

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

C#

1

2

3

4

5

6

7

"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:

C#

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

Root

--id

--name

--pref

---+++pref's property 1

---+++ pref'sproperty2

--membership

---+++membership object1

---+++---object1's property 1

---+++--- object 1'sproperty2

---+++membership object2

---+++---object2's property 1

---+++--- object 2'sproperty2

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.

C#

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

classMembership

{

publicstringId{get;set;}

publicstringIdMember{get;set;}

...

}

classTrello

{

publicstringId{get;set;}

publicstringName{get;set;}

...

publicPrefPrefs{get;set;}

...

publicList<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.

C#

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

"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:

C#

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

Root

--id

--name

...

--card

---+++card object1

---+++---object1's property 1

---+++--- object 1'sbadge

---+++---+++badge's property 1

---+++---+++ badge'sproperty2

---+++---object1's checkItemStates object

---+++---+++ checkItemState object 1

---+++---+++--- property IdCheckItem

---+++---+++--- property State

---+++---+++ checkItemState object 2

---+++---+++--- property IdCheckItem

---+++---+++--- property State

...

---+++ card object 2

---+++--- object 2'sproperty1

---+++---object2's badge

---+++---+++ badge'sproperty1

---+++---+++badge's property 2

---+++--- object 2'scheckItemStates object

---+++---+++checkItemState object1

---+++---+++---property IdCheckItem

---+++---+++---property State

---+++---+++checkItemState object2

---+++---+++---property IdCheckItem

---+++---+++---property State

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

C#

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

classCheckItemState

{

publicstringIdCheckItem{get;set;}

publicstringState{get;set;}

}

classBadge

{

publicstringVotes{get;set;}

publicstringViewingMemberVoted{get;set;}

}

classCard

{

publicstringId{get;set;}

publicBadgeBadges{get;set;}

publicList<CheckItemState>CheckItemStates{get;set;}

...

}

classTrello

{

publicstringId{get;set;}

publicstringName{get;set;}

...

publicPrefPrefs{get;set;}

...

publicList<Membership>Memberships{get;set;}

publicList<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.

C#

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

usingSystem;

usingSystem.IO;

usingSystem.Web.Script.Serialization;

namespaceTrelloJson

{

classProgram

{

privateconststringJsonFile=@"trello-json-testing.json";

staticvoidMain()

{

// store all data from the given file into a data variable

vardata=File.ReadAllText(JsonFile);

// deserialize data. After deserialization, our object json will be

// populated with information from JSON file

varserializer=newJavaScriptSerializer();

varjson=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.