Decoding a json with for each

I have this json, and I want to extract for each entity the entity id, the value, and the confidence. I am using a for each (item in json) and item.name for the entity id which is working fine. However, much trial and error has not helped me extract the value or confidence.

Normally I’d name the fields I want from the deserialised json, eg json.root(“sentiment”).item(“value”).value(of string). However, this does not apply in the for each scenario, when the number and types of entity could change. Furthermore, this only goes two levels down, and I require 3 levels for this.

{
“_text”: “i am really angry”,
“entities”: {
“sentiment”: [
{
“confidence”: 0.81935781644837,
“value”: “negative”
}
]
},
“msg_id”: “1aqeYcAE9yMVMCtuW”
}

Any help is appreciated.

This component will save you a lot of trouble parsing the data.

This is interesting, but it’s not quite what I wanted. This will work well for mass entries, but I have single calls and I need to be able to use the values in the output more easily.

The output this gives me is:

_text entities/sentiment/0/confidence entities/sentiment/0/value msg_id
I am excited to work 0.981648564 positive 1bdu1ZV0isn9tkpeh

However, I need something more akin to:

Entity ID Value Confidence
Sentiment positive 0.981648564

I am assuming every entity will have a value and confidence result, but I do not know how many entities I will return (and indeed I may add more later and want my workflow to be easy to maintain).

I would consider using this as a holding table, but I’d end up with a pair of headers for each entity, and this would quickly become problematic to convert to my format.

you can do a for each on the sentiment array, after deserializing the response…

2 Likes

But there could be multiple entities, and I do not know how to read from the subarray (what type do I need, for example?)

the sentiment is going to be a JArray…

19.4.3+Branch.support/v2019.4.Sha.a8f4efb75012dc834aa4be501a7086e9372ef1b3

Source: Deserialize JSON Array

Message: Error reading JArray from JsonReader. Current JsonReader item is not an array: String. Path ‘’, line 1, position 11.

Exception Type: Newtonsoft.Json.JsonReaderException

An ExceptionDetail, likely created by IncludeExceptionDetailInFaults=true, whose value is:
Newtonsoft.Json.JsonReaderException: Error reading JArray from JsonReader. Current JsonReader item is not an array: String. Path ‘’, line 1, position 11.
at Newtonsoft.Json.Linq.JArray.Load(JsonReader reader, JsonLoadSettings settings)
at Newtonsoft.Json.Linq.JArray.Parse(String json, JsonLoadSettings settings)
at UiPath.Web.Activities.DeserializeJsonArray.Execute(CodeActivityContext context)
at System.Activities.CodeActivity.InternalExecute(ActivityInstance instance, ActivityExecutor executor, BookmarkManager bookmarkManager)
at System.Activities.ActivityInstance.Execute(ActivityExecutor executor, BookmarkManager bookmarkManager)
at System.Activities.Runtime.ActivityExecutor.ExecuteActivityWorkItem.ExecuteBody(ActivityExecutor executor, BookmarkManager bookmarkManager, Location resultLocation)

My flow looks like:

The response is not an array, just that part… Use Deserialize Json for the response…

Yeah, I’m using deserialise json for the response, then for each entity I am trying to get the name, the value and the confidence. Sentiment is an entity, and you said this is an array.

Here is the start of my flow:

I’ve left json to datatable in in case somebody could help me use that in the manner I need.

I have tried to use assign to a string variabel with
jsonObject.SelectToken(“entities."+entityID+".value”).ToString

but I get object reference not set to an instance of an object

if i deserialise jsonObject(“entities”).tostring into a new JObject, and use my usual .root(name).item(name).value(of string) then I get an error Assign: Accessed JArray values with invalid key value: “value”. Int32 array index expected.

if your response is like you showed us, entities are NOT an array…

I know it’s not an array.

Here is a bigger response, which shows how entities change depending on the input:

{
“_text”: “tomorrow will be good”,
“entities”: {
“datetime”: [
{
“confidence”: 0.96583,
“values”: [
{
“value”: “2020-03-13T00:00:00.000+00:00”,
“grain”: “day”,
“type”: “value”
}
],
“value”: “2020-03-13T00:00:00.000+00:00”,
“grain”: “day”,
“type”: “value”
}
],
“sentiment”: [
{
“confidence”: 0.83626997064468,
“value”: “positive”
}
]
},
“msg_id”: “1t51fNxv1VzJXvGig”
}

In this case I want an output table of:

Entity ID Value Confidence
datetime 2020-03-13T00:00:00.000+00:00 0.96583
Sentiment positive 0.836269971

but if you know, why you are trying to do a for each on it?

please see above.

So because there can be multiple entities, I need to use a For Each so I can get whichever types there are.

Under each entity ID there is at least “value” and “confidence”. These are the items I want to put into my data table as shown.

do you see that { after entities? It means is not an JArray…

so it would appear that sentiment is an array, but not really.

you can see this in the json to data table extract that includes the int32 index 0. perhaps I need to use this?

I’m not assigning JArray anywhere.

for each sets as JProperty