Extract data from JSON based on an HTTP request

Help Activities
, , , ,
1

Dear Uipath community

Hope you are all doing great.
I started using the tool few days ago and I would need your help team.

After spending an entire trying to extract data from a JSON using Deserialize/DeserializeArraw/Jobject/Jarray…, I have to admit that I need people like you with experience and knowledge.
I am trying to extract data(“IdColl”) by looping in a JSON based on a HttpRequest.

Below is an example of the JSON and within it, I have several blocs of data within each the “IdColl” exists. I need to retrieve each IdColl. Could you please share your wisdom ?

A big thank your

3

Hi @KrisUi

Use Deserialize JSON activity. It will give you an out put of type JsonObject. Lets us say you store the JsonObject to a variable jsonObject, for example. Now you can get values from the JsonObject like below.

jsonObject(“IdColl”).ToString

Kind regards,
Kenneth

4

I have tried but never obtained the expected outcome.
This is the JSON
[
{
“_id”: “6356b1a9f2ea4e7c42e3be74”,
“idInstrument”: 1,
“referenceDatetime”: “2022-09-09T15:18:37.000+00:00”,
“schemaName”: “ept”,
“schemaVersion”: “2.0”,
“idTransaction”: “7fd44a17-a7ad-46be-85c9-6562a6c0d15f”,
“createDatetime”: “2022-10-24T15:39:16.270+00:00”,
“active”: true,
“user”: “integration”,
“source”: “fake”,
“Disclosure”: “Public”,
“IdAbontColl”: 2205,
“IdColl”: 10101,
“IdDeposant”: 5612,
“IdHistoAbontColl”: 7356,
“IdInstr”: 1,
“PE00005_FileGenerationDateAndTime”: “2022-09-09T15:18:37.000+00:00”,
“PE00030_PortfolioIdentifyingData”: “LUTS01TC09”
}
]

5

When I try deserirializing it I obtain this error
Deserialize JSON: Deserialized JSON type ‘Newtonsoft.Json.Linq.JArray’ is not compatible with expected type ‘Newtonsoft.Json.Linq.JObject’. Path ‘’, line 1, position 1261.

6

Hi @KrisUi

I think it’s because your json is inside square brackets making it JArray not a JObject.

7

Thank you for the feedback.
With Jarray, when I use Writeline activity JSONLastObjectArra(“IdColl”).ToString
I receive the message
Write Line: Accessed JArray values with invalid key value: “IdColl”. Int32 array index expected.

8

How can I extract this value for IdColl in order to store it for example in an excel?

9

Try JSONLastObjectArra[1](“IdColl”).ToString or JSONLastObjectArra(1)(“IdColl”).ToString

10

Hi @KrisUi ,

The data that you have is a JArray, perhaps you would require it’s content directly in the form of a Datatable ? If so, You could use the below Expression :

Newtonsoft.Json.JsonConvert.DeserializeObject(Of System.Data.Datatable)(dataText)

Where dataText would be the provided JArray data.

image
image757×492 14.8 KB

Let us know if this is what was required else provide some more info what output format you need / If it is a Collection of values or a Single value extraction.

11

Hi guys,
In the end I played with JArray combined with a Regex to extract the data I needed
Thanks again

1 Like
12

Hi @KrisUi ,

Could you maybe provide a more descriptive details of the solution used ?

Providing the expressions used for Using the JArray and the Regex method would be helpful for others who are also facing the same issue. :slightly_smiling_face:

1 Like
13

Really appreciate

14

I have a code here that can help you.

In general when we have a Json Array (Starts with “[”) we need to Deserialize the JsonArray so:

  • Pass the JSON as a String
  • It will return a JArray variable
  • Now you can Deserialize this Jarray variable based on its index, for example ReturnJarray(0).Tostring
  • It will return a JObject, in general i like to work with Dictionary(String, Object), so i create a dictionary variable and receive the output from the Deserialize JSON
  • With it you can get a value from your JSON based on a key: value, for example: IdColl = YourDictionary(“IdColl”).ToString

Note: Always check if it has another JArray inside, if thats the case, repeat the process

Probably we have a lot of ways to do that, but i’m sharing one i frequently use

GetJsonValue.zip (95.0 KB)

1 Like
15

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.