Get info from a "incorrect" JSON in Deluge

Question

I am having a bit of trouble with a JSON provided by Zoho Analytics API. When you consult a table, the JSON is not well formed and I am not able to get all the info in Deluge.

The JSON is this way. When ID is not empty, the next ones are related to it but they have ID empty. I want to get the Total sum related to that ID in Deluge. For example, for Product1 I want to sum the first three total.

I tried with a for each in Deluge but I am not able to identify when is a new "data group"

{
   "ID":"81921000005496719",
   "Name":"Product1",
   "Total":"110.00"
},
{
   "ID":"",
   "Name":"",
   "Total":"94.00"
},
{
   "ID":"",
   "Name":"",
   "Total":"44.00"
},
{
   "ID":"81921000005496749",
   "Name":"Product2",
   "Total":"150.00"
},
{
   "ID":"",
   "Name":"",
   "Total":"322.00"
},
{
   "ID":"",
   "Name":"",
   "Total":"84.00"
},

Answer 1

The Deluge List does not guarantee to be ordered in the same JSON order. You have to change the Analytics query to include the same Name or ID for the related lines, so you can easily loop over the data.

void test()
{
    data = {{"ID":"81921000005496719","Name":"Product1","Total":"110.00"},{"ID":"","Name":"Product1","Total":"94.00"},{"ID":"","Name":"Product1","Total":"44.00"},{"ID":"81921000005496749","Name":"Product2","Total":"150.00"},{"ID":"","Name":"Product2","Total":"322.00"},{"ID":"","Name":"Product2","Total":"84.00"}};
    sums = Map();
    for each  line in data
    {
        x = sums.get(line.get("Name"));
        if(x == null)
        {
            x = 0.0;
        }
        sums.put(line.get("Name"),line.get("Total").toDecimal() + x);
    }
    info sums;
}

Answer 2

Based on your current JSON data. you can sum the first 3 totals. I modify the first answer and the code should be

raw_json_data = {{
   "ID":"81921000005496719",
   "Name":"Product1",
   "Total":"110.00"
},
{
   "ID":"",
   "Name":"",
   "Total":"94.00"
},
{
   "ID":"",
   "Name":"",
   "Total":"44.00"
},
{
   "ID":"81921000005496749",
   "Name":"Product2",
   "Total":"150.00"
},
{
   "ID":"",
   "Name":"",
   "Total":"322.00"
},
{
   "ID":"",
   "Name":"",
   "Total":"84.00"
 },
 {
   "ID":"8192100000549000",
   "Name":"Product3",
   "Total":"100.00"
},
{
   "ID":"",
   "Name":"",
   "Total":"32.00"
},
{
   "ID":"",
   "Name":"",
   "Total":"4.00"
 }};
result = Map();
process_json_data = List();
initial_index = 0;

for each data_rec in raw_json_data{
    initial_id = raw_json_data.get(initial_index).toMap().get("ID");
    initial_name = raw_json_data.get(initial_index).toMap().get("Name");
    new_data = data_rec.toMap();
    if (data_rec.get("ID") == ""){
        new_data.put("ID",initial_id);
        new_data.put("Name",initial_name);
    }
    else{
        initial_index = raw_json_data.indexOf(data_rec);
    }
    process_json_data.add(new_data);
    
}
sums = Map();
for each  line in process_json_data
{
    x = sums.get(line.get("Name"));
    if(x == null)
    {
        x = 0.0;
    }
    sums.put(line.get("Name"),line.get("Total").toDecimal() + x);
}
info sums;

And the result is

{"Product1":248.00,"Product2":556.00,"Product3":136.00}

You can test these script in https://dre.zoho.com/tryout

Thanks, Von