Deserialize JSON Array nested list to excel?

Hey,
I have a json array looking like this:
[
{
“id”: 9,
“number”: 1000025,
“email”: “blabla”,
“name”: “Dan”,
“employment_date”: “2006-03-27”,
“latest_shift_date”: “2020-02-27”,
“shift_count”: {
“30”: 1,
“60”: 5,
“90”: 10
},
“first_aid_expire”: null,
“emergency_team_member”: false,

problem is, when im using the for each, i dont got all the data from the “shift count” list. How do I do that with a nested list? Im convertering the data into excel.

shift_count is another jsonarray in your main jsonarray.
You’d have to build 2 for each loops nested in eachother. The inner loop going through the shift_count node.

1 Like

Hey Jeroren!
Yes i was thinking about that as well! But i cant figure it out how. Can you please give me an example of that?

Hi,

your sample json is not a valid json and it is throwing error. Could you please share your sample full valid json. i will try at my end. thanks.

Sample Work flow skeleton attached. please review and see if it helps for your requirement.

JsonDemo.zip (2.3 KB)

1 Like

jsonarray.json (1.6 KB)

here is the file. make notice that there is one more nested list in the json array, called “location_types”.
If you can make a Uipath file, i would appreciate it a lot!

tried with both nested list, but cant work it out. Let me know if you can @kirankumar.mahanthi1 :slight_smile:

Not able to convert Json string to Json object throwing the below error. trying out some other options.

Deserialize JSON: Unable to cast object of type ‘Newtonsoft.Json.Linq.JArray’ to type ‘Newtonsoft.Json.Linq.JObject’.

All right, let me know if you can make it happen :smiley:

Hi,

Sure. one question from my side. the attached Json is converted one or raw json.
if you have raw json file could share it with me.

i have checked with other sample json i am able to convert them to json object by using deserialize json activity. But for the attached json i am not able to convert.

The json file is actually coming from a http request, but i cant share it with you, because there is some authentication. but the json file ill shared with you, is the same as from the http request. I might think the problem is because its a json array? - Does it make sense?

yeah since it is json array it is not able to convert to json object.

does it help if you put “.ToArray” in the end?

Nope. it is not working.

your provided json file can be serialized as an JArray:
grafik

the items we can pass for looping within a for each activity - TypeArgument set to JObject
grafik

Values from the properties (when simple datatypes) we can access as usually
grafik

Using this essential Buildings blocks the data extraction can be implemented and has also potential to organized within a meore generic, dynamic way

Hi @christian.bendtsen
JSON.xaml (7.9 KB)

Workflow attached. Hope it helps.