JSON Array with multiple json objects and json arrays

hello,

I made an API call and I got a json response. I deserialized the json response, so that I have a json object. Now I want to extract some information from the object, but I have not managed to do it.

Here is my json response:

{
“results”: [
{
“group”: {
“mediaType”: “chat”,
“queueId”: “7a982920-3a-4083-8315-db0f5fec6c44”
},
“data”: [
{
“interval”: “2021-08-16T00:00:00.000Z/2021-08-22T00:00:00.000Z”,
“metrics”: [
{
“metric”: “tAcw”,
“stats”: {
“max”: 800000,
“min”: 6000,
“count”: 4,
“sum”: 944000
}
},
{
“metric”: “tAlert”,
“stats”: {
“max”: 15109,
“min”: 5775,
“count”: 10,
“sum”: 118730
}
},
{
“metric”: “tAnswered”,
“stats”: {
“max”: 458033,
“min”: 6279,
“count”: 4,
“sum”: 562876
}
},
{
“metric”: “tHeld”,
“stats”: {
“max”: 840516,
“min”: 1888,
“count”: 17,
“sum”: 1955572
}
},
{
“metric”: “tTalk”,
“stats”: {
“max”: 1853632,
“min”: 3257,
“count”: 19,
“sum”: 6281077
}
}
]
}
]
},
{
“group”: {
“mediaType”: “chat”,
“queueId”: “818263b4-2f9e-4d02-bbbe-c36b5d4725”
},
“data”: [
{
“interval”: “2021-08-16T00:00:00.000Z/2021-08-22T00:00:00.000Z”,
“metrics”: [
{
“metric”: “tAbandon”,
“stats”: {
“max”: 9506,
“min”: 9506,
“count”: 1,
“sum”: 9506
}
}
]
}
]
},
{
“group”: {
“mediaType”: “chat”,
“queueId”: “dc880530-ddae-4b57-b657-c3c69ccdec49”
},
“data”: [
{
“interval”: “2021-08-16T00:00:00.000Z/2021-08-22T00:00:00.000Z”,
“metrics”: [
{
“metric”: “tAbandon”,
“stats”: {
“max”: 180197,
“min”: 4581,
“count”: 2,
“sum”: 184778
}
},
{
“metric”: “tAcw”,
“stats”: {
“max”: 384000,
“min”: 4000,
“count”: 6,
“sum”: 965000
}
},
{
“metric”: “tAlert”,
“stats”: {
“max”: 15117,
“min”: 2120,
“count”: 8,
“sum”: 61087
}
},
{
“metric”: “tAnswered”,
“stats”: {
“max”: 566650,
“min”: 5876,
“count”: 6,
“sum”: 835086
}
},
{
“metric”: “tHeld”,
“stats”: {
“max”: 837516,
“min”: 4538,
“count”: 17,
“sum”: 2011554
}
},
{
“metric”: “tTalk”,
“stats”: {
“max”: 1015542,
“min”: 1741,
“count”: 21,
“sum”: 5173385
}
}
]
}
]
},
{
“group”: {
“mediaType”: “chat”,
“queueId”: “fec05e6c-6c00-45e6-8e08-47b778”
},
“data”: [
{
“interval”: “2021-08-16T00:00:00.000Z/2021-08-22T00:00:00.000Z”,
“metrics”: [
{
“metric”: “tAbandon”,
“stats”: {
“max”: 109467,
“min”: 10526,
“count”: 3,
“sum”: 3904
}
},
{
“metric”: “tAcw”,
“stats”: {
“max”: 2727000,
“min”: 2000,
“count”: 26,
“sum”: 37000
}
},
{
“metric”: “tAlert”,
“stats”: {
“max”: 15145,
“min”: 2097,
“count”: 32,
“sum”: 2665
}
},
{
“metric”: “tAnswered”,
“stats”: {
“max”: 607393,
“min”: 2656,
“count”: 26,
“sum”: 13038
}
},
{
“metric”: “tHeld”,
“stats”: {
“max”: 827900,
“min”: 1274,
“count”: 77,
“sum”: 45075
}
},
{
“metric”: “tTalk”,
“stats”: {
“max”: 1189204,
“min”: 1400,
“count”: 98,
“sum”: 168065
}
}
]
}
]
}
]
}

I want to extract from each “group” the value for “queueId” and for each “queueId” the “metric” and for each “metric” the “count”.

I am aware I have to do a loop, but somehow it has not worked. Has anyone an Idea? I would be really grateful!

Hi @regina2112,

There is some issue in this json, I am unable to format it. But code in loop should be something like below, please give a try:

Different groups in Json object can be retrieved as below:

For each item in jsonObj.SelectToken(“results”)
and then for nested groups, use inner for each loop like For each item in jsonObj.SelectToken(“group”) or
For each item in jsonObj.SelectToken(“data”)
wherever you want to retrive values from.

Inside for each loop, fetch values.

Regards
Sonali

Hi Sonali,

Thank you for your quick response. However, unfortunately it did not work.

An error message “For each: Object reference not set to an instance of an object” appears.

Best regards,
Regina

gve us some little time currently we have a look on it. Extraction into a datable would work for you?

Yes that would be perfect. Thanks

Hi @regina2112 ,

You can try the attached workflow for extracting the json data.

extractjson.xaml (8.3 KB)

Thanks,
Manjula

@regina2112

preperation:
grafik

Flow:

Result:
grafik

Concepts:
Array Item retrieval with the values(Of DataType)
relative retrieval of the metrics with the getAllDescendants syntax “…PropertyName”
proper datatype value retrieval with the Jtoken.Value.Value(of DataType) access

Find starter help here:
ppr_JSONBox_Regina2112.xaml (11.8 KB)

For some docu on the usable of XPath like syntax on SelectToken also check:
https://goessner.net/articles/JsonPath/

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