How to filter by sum of items in nested array

#1

08 PM

On a DataLoadProcessLog type like above, how do I filter the items whose sum of createdObjCount + updatedObjCount over all targetTypeStats is 0?

0 Likes

#2

From the documentation of filter in FetchFilterSpec (use c3ShowType(FetchFilterSpec)) you can read:

Expressions can include fields in the Obj itself (e.g. “name == ‘foo’”), as well as fields in Objs in other types refereneced from the Obj (e.g. “refField.name == ‘foo’”). Expressions involving arry/mapp fields will automatically be interpreted as “exists” queries (e.g. “refArry.name == ‘foo’” will bring back all Obj instances that have at least one entry in refArry where the referenced Obj has the value ‘foo’ for its name field).

So in your case, you can use something like:

DataLoadProcessLog.fetch({filter: 'stats.targetTypeStats.createdObjCount + stats.targetTypeStats.updatedObjCount==0'})
0 Likes

#3

That doesn’t seem to work. Here is an item I see when I filter with your query. As you can see, createdObjCount!=0 for RegisterMeasurement. I need all of the items to be 0.

35 PM

DataLoadProcessLogs are populated when they are processed. That is why they don’t always have targetTypeStats

0 Likes

#4

The query is right because even if one of the elements in targetTypeStats has createdObjCount + stats.targetTypeStats.updatedObjCount == 0 it returns the DataLoadProcessLog record

The reason it is not returning the expected results because targetTypeStats is not a reference to an entity type.

So you might need to process the objects with something hacky in java script.

DataLoadProcessLog.fetch({limit:100, filter: "stats.targetTypeStats.createdObjCount == 0"}).objs.map(dpl=>{
  var targetTypeStats = dpl.stats.targetTypeStats;
  var newTargetTypeStats = {}
  var keys = _.keys(targetTypeStats);
  _.each(keys, function(key){
   var stat = targetTypeStats[key];
   if (stat.createdObjCount + stat.updatedObjCount == 0)
      newTargetTypeStats[key] = targetTypeStats[key]; 
   });
  var newStats = DataLoadProcessStats.make(dpl.stats);
  newStats.targetTypeStats = newTargetTypeStats;
  return dpl.putField("stats", newStats);
})
1 Like

#5

Hopefully with the introduction of SourceFile in 7.7 targetTypeStats is associated to the sourceFile record itself, and the query will not be as complex above.

1 Like

#6

It would be nice to enable jsonPath/xpath filters. A jsonPath filter for this problem this would look as follows

DataLoadProcessLog.fetch({filter: '$.targetTypeStats[?((@.createdObjCount + @.updatedObjCount)==0)]'})

0 Likes