从“不正确”的地方获取信息Deluge 中的 JSON

Question

我在使用 Zoho Analytics API 提供的 JSON 时遇到一些问题。当您查阅表格时，JSON 的格式不正确，我无法获取 Deluge 中的所有信息。

JSON 是这样的。当ID不为空时，后面的与其相关但ID为空。我想获得与 Deluge 中该 ID 相关的总和。例如，对于 Product1，我想将前三个总数相加。

我尝试在 Deluge 中使用 for every ，但我无法确定何时是新的“数据组”

{
   "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

基于您当前的 JSON 数据。您可以将前 3 个总数相加。我修改第一个答案，代码应该是

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;

结果是

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

您可以在 https://dre 中测试这些脚本。 zoho.com/tryout

谢谢，
冯

Answer 2

Deluge List 不保证以相同的 JSON 顺序排序。您必须更改 Analytics 查询以包含相关行的相同名称或 ID，以便您可以轻松地循环数据。

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;
}