Put nested json in datatable with newtonsoft

[narda185]

Hi everyone,
I have a nested json document, that I want to put into a datatable. The json is quite big (>100.000 entries and >30 columns), so looping through every entry is pretty time consuming. Since I have to do this with multiple endpoints it adds up and I am trying to find a more efficient way to handle the json.

I tested to convert the json with Newtonsoft.Json.JsonConvert.DeserializeObject(of DataTable)(jsonString) which was very fast even though the json is big, but I couldn’t find how I can put the nested data in the data table as well. Right now it creates an empty column for that.

Here is a json example that is similar to the data I have:
[
{
“id”:1,
“name”: “first entry”,
“array”: [
{
“arrayItemId”:1,
“arrayItemName”:“first array item”
},
{
“arrayItemId”:2,
“arrayItemName”:“second array item”
}
]
},
{
“id”:2,
“name”: “second entry”,
“array”: [
{
“arrayItemId”:1,
“arrayItemName”:“first array item”
}
]
}
]

When I convert this with newtonsoft it gives me a data table with these columns:
id, name, array
I need a data table with these columns:
id, name, arrayItemId, arrayItemName

If possible, I would also love, if I can duplicate columns. So in reality the data table needs to look like this:
id, name, name, arrayItemId, arrayItemId, arrayItemName
The question here is, if this is possible and if yes, if this is better than just duplicating the column with other means.

[sven.wullum1]

Hi!

If you are comfortable working with coded workflows I highly recommed using it to build a class that you can use to deserialize the JSON-string with.
Records - C# reference | Microsoft Learn

For your example
Create a coded source file named:

using System.Collections.Generic;
//don't change the namespace that is autogenerated by your source file
namespace YourNamespace
{
      public record ArrayItem
      (
          int ArrayItemId,
          string ArrayItemName
      );
      public record MainEntry
      (
          int Id,
          string Name,
          IEnumerable<ArrayItem> Array
      );    
}

See how the main entry contains a list of ArrayItem which again contains your actual items.

Then in your regular workflow just put in an assign

Newtonsoft.Json.JsonConvert.DeserializeObject(IEnumerable(of MainEntry))(jsonString)

The result of which will be stored in an object of type MainEntry which you can then traverse.

[narda185]

I wasn’t able to make it work. I tried that, but it threw an error with the IEnumerable(of MainEntry), so I did it with just (of MainEntry) and tried it with a for each to loop through the entries, but then I didn’t know how to put the data from the custom object into a data table.

Also I am not sure how this makes the processing much faster as there would be a for each involved in all instances I could think of. When I use “Newtonsoft.Json.JsonConvert.DeserializeObject(of DataTable)” it is extremely fast, even with many rows, but as soon as I need to utilize a for each and loop through all rows, it would probably not be that fast anymore.

[sven.wullum1]

You can just use your custom object.
You don’t need to put it into a datatable unless you really have to.

What was your error?

[narda185]

Well I could get it to work like so: Newtonsoft.json.JsonConvert.DeserializeObject(of IEnumerable(of MyNamespace.MainEntry))(jsonString) and by defining the variable as an IEnumerable of MainEntry, but it always gives me an empty object when I try to read it with “entry.ElementAt(0).ToString” the result ist “MainEntry { }”

I tried to write my own ToString method: public override string ToString() {
string output = id + “,” + name;
return output;
}
but this just returns “0,” (same for ElementAt(1)).

Since there is no error message, it is pretty hard for me to find what I’ve done wrong.

[sven.wullum1]

You have a collection of Main Entries, which in turn contains a collection of arrayItems.
You don’t need to write a function for printing any output.

I’ve taken your example JSON:

[
	{
		"id":1,
		"name": "first entry",
		"array": 
		[
			{
			"arrayItemId":1,
			"arrayItemName":"first array item"
			},
			{
			"arrayItemId":2,
			"arrayItemName":"second array item"
			}
		]
	},
	{
		"id":2,
		"name": "second entry",
		"array": 
		[
			{
			"arrayItemId":1,
			"arrayItemName":"first array item"
			}
		]
	}
]

I’m deserializing into the records above:

JsonConvert.DeserializeObject(of IEnumerable(of MainEntry))(strJSON)

If i want to access this “item”

I’d have to access the first MainEntry and the first ArrayItem in that first MainEntry like so:

ListOfMainEntries.ElementAt(0).Array.ElementAt(0).ArrayItemName

[narda185]

Thank you for taking the time to explain it again. I am afraid, I must have done something wrong though, but I can’t find the error. I added a screenshot from my instance, so that you can see the error and maybe point me to the thing I missed.
When I try to access the ArrayItemName it says Array is not a member of MainEntry.

[sven.wullum1]

No problem!

I can’t really tell what’s wrong, everything looks ok.
Coded Workflows and namespaces can be a bit buggy to work with in some studio versions. Try closing the project, delete the hidden .local folder in your project folder and open the project again.

If that doesn’t work type this in the expression part of your log message:

entries.ElementAt(0).

and after the ‘.’ press CTRL+SPACE.
This will bring up the available functions and structures in your object:

Take a screenshot of what’s available.

[sven.wullum1]

I know what’s wrong now, you’re using curly brackets instead of parentheses

Copy this:

using System.Collections.Generic;

namespace BaseTables
{
    public record ArrayItem
      (
          int ArrayItemId,
          string ArrayItemName
      );
      public record MainEntry
      (
          int Id,
          string Name,
          IEnumerable<ArrayItem> Array
      );    
}

[narda185]

I knew it was something like this xD Thank you very much for helping me solving this. It works now.