Extraction of Data from a JSON

[TimoC]

Hi Guys,

I use UiPath since around two weeks. With the integrated functions it works for me as user fine.
Now i have a new requirement and actually i have not a clue to solve it. May be somebody can give me some tips:

Situation:
I actually use UI Path to extract json Information from a Partner API and write it to a csv/text file (around 5mb). Works fine. I got now a request if it is possible to filter the Information since colleagues want to put that informations in a SQL database… It means to get the complete json Feedback and filterering for several 100 entrys (dynamic size) with around an dozen different informations (like Name, prize, Duration, and so one)

Idea:
I thought that i can build an loop to search the file for different key words / informations. But i did’t found an option for that.
Any tips?

Regards Timo

[badita (Mihai Badita)]

Not really an answer but I have created an automation challenge here so stay tuned. I guess that is what you’re looking for.

[aksh1yadav (AKSHAY YADAV)]

hey @TimoC

4. RPA Challenge - Json Parsing

Like I said, there’s no error It’s just that the whole file is not a strict JSON, but a JSON array in itself - if you use Deserialize JSON Array activity instead, it’ll work. Solution attached - Linq and SelectToken variants. Sidenote - for better view if it’s correct change the phone numbers in the file so they’re not all identical. Main.xaml (9.5 KB)

Regards…!!

[TimoC]

Thanks for the Input.
I will try to adapt that for my case,

Thanks!

regards
Timo

[TimoC]

Hi Guys,

I still have some Problems with the orignal Operation. The JSON part is now okay. But i have still Problems to write the extracted JSON informations to csv file.
The Export to a csv presume that the data Comes from a datatable. How can i Transfer data from the json Export to a datatable?

Regards
Timo

[aksh1yadav (AKSHAY YADAV)]

hi @TimoC

if you are able to extract json data if i will suppose kind of array you are getting then you can use loops with it according to datatype suitable and by using “add data row” datatable activity you can add data into a datatable and then you can write into an excel or csv.

Regards…!!

[TimoC]

Hi Aksh1

Thanks for your Feedback.
My sequence actually Looks like:

1. HTTP Request (get a id)
2. HTTP Request with the ID ( get the json as string)
3. Deserialize Json (string) as Json object
4. For each Loop
5. Write it to line as string

That is working so far (if i filter that everything will be exported). If i Change the filtering to dífferent types i got an error that there are different datatypes in the Array (string, int32 and so on). Think here is the first understanding Problem from me.

I tried to Change the step 3 to a json Array deserialization but than i receive an exeption that the json string is not an array (that isn’t true, that is an JasonArray). So i changed it deserialize json and deserialze it. Is that the reason why i can not Transfer the data to a datatable?

Regards
TimoMainJsonArray.xaml (6.6 KB)
MainJsonObject.xaml (6.5 KB)

[TimoC]

I exported a part of my test xaml files. May be that is simpler for the understanding…

Regards
Timo

[Senthilkumar55 (Senthilkumar V)]

HI,
I deserialized my JSON file.But I’m getting the following error. I don’ know why am I getting this…?

“Error reading JObject from JsonReader. Current JsonReader item is not an object: StartArray. Path ‘’, line 1, position 1.” Can anyone help me to solve this.

[Luke_Angel]

download the web plugin

[Luke_Angel]

[sarthakj (sarthak jain)]

@Senthilkumar55
JSON array must start with square brackets"[" and should end with “]”, your JSON array might be wrapped up in another JSON object which is not an array, for eg.

This is correct:
[{“number”:“INC0355531”,“incident_state”:“Pending Change”},{“number”:“INC0325709”,“incident_state”:“Pending Third Party”},{“number”:“INC0355477”,“incident_state”:“Pending Change”},{“number”:“INC0194842”,“incident_state”:“Pending Third Party”}]

This is incorrect:
{“result”:[{“number”:“INC0355531”,“incident_state”:“Pending Change”},{“number”:“INC0325709”,“incident_state”:“Pending Third Party”},{“number”:“INC0355477”,“incident_state”:“Pending Change”},{“number”:“INC0194842”,“incident_state”:“Pending Third Party”}]}

[arivu96 (Arivazhagan A)]

Hi @sarthakj,

This is incorrect:

{“result”:[{“number”:“INC0355531”,“incident_state”:“Pending Change”},{“number”:“INC0325709”,“incident_state”:“Pending Third Party”},{“number”:“INC0355477”,“incident_state”:“Pending Change”},{“number”:“INC0194842”,“incident_state”:“Pending Third Party”}]}

This also valid JSON format only.

Regards,
Arivu

[sarthakj (sarthak jain)]

Yes, I know but this is not a JSON array, that’s what i wanted to say.

[Atrimal]

Don’t use deserialise,
Create new variable and parse string
JObject works when we use variable name “jobject” and variable type jobject with Default value to “new jobject”

[karthik_bethi (karthik)]

Hi
Pls follow below json parsing tutorial

[sharathraju489 (Sharath Raju)]

Here is a detailed article on that

How To Extract The Data From A JSON - In UiPath - ExcelCult