How can I subtract an array of values in a MongoDB query?

Question

I have a collection of documents, where one of the fields is an array of 64 numbers. I would like to construct a query that returns these documents, where this array has been subtracted and summed, however, I cannot figure out how to do even the subtraction portion of this task.

An example document might look like this.

{
"_id" : ObjectId("577be558033ad33f66684974"),
"syntactic_fingerprint" : [ 
    0.921382546424866, 
    0.048630952835083, 
    -0.251873761415482, 
    -0.0470362603664398, 
    0.0, 
    0.0296048410236835, 
    0.0319229736924171, 
    -0.0332595892250538, 
    -0.0694081708788872, 
    -0.117949850857258, 
    -0.0555221512913704, 
    -0.176394507288933, 
    0.0160530339926481, 
    -0.0234932824969292, 
    -0.0148191852495074, 
    0.0816841721534729, 
    0.0323052480816841, 
    0.0, 
    0.0, 
    0.0142431678250432, 
    0.0, 
    0.0, 
    0.0189650449901819, 
    0.0386682450771332, 
    0.0308650359511375, 
    0.0, 
    0.0310780759900808, 
    0.0361337624490261, 
    0.0, 
    0.0, 
    0.0, 
    0.0128832636401057, 
    -0.0125190699473023, 
    0.0, 
    -0.0282401368021965, 
    -0.0348126254975796, 
    -0.0154333971440792, 
    -0.0173368379473686, 
    -0.0443549081683159, 
    -0.0181444175541401, 
    -0.0224117543548346, 
    0.0, 
    -0.0689809918403625, 
    0.0, 
    -0.0300765186548233, 
    0.0, 
    0.0, 
    -0.0184972882270813, 
    0.0, 
    0.0, 
    0.0, 
    -0.0128712980076671, 
    0.0, 
    0.0, 
    0.0183296073228121, 
    -0.017868610098958, 
    0.0, 
    0.0, 
    -0.0145018044859171, 
    0.0134829748421907, 
    -0.0138665018603206, 
    0.0, 
    0.0, 
    0.0
]
}

I am trying to construct a query that will take one of the 64 element "syntactic_fingerprint" vectors (as a constant), and subtract it from the fingerprint of every other document, then sum the values of the subtracted fingerprints into a single number.

This seems like it ought to be possible somehow with the aggregate pipeline... but I can't seem to find an elegant way to do it.

Answer 1

You will indeed need to use an aggregation pipeline to do this. It should be relatively simple, you just need to $unwind the array, which will create a document for each element in the array for the next stage in the pipeline. You can then group that down into a single result and accumulate the sum of values in the document, as well as subtracting your constant from each element.

Example(mongoshell js):

db.myCollection.aggregate([
{
  $match: {syntactic_fingerprint: myConstant}
},
{
  $unwind: '$syntactic_fingerprint'
},
{
  $group: {
    _id: null,
    sum: {$sum: {$subtract: ['$syntactic_fingerprint', myConstant]}}
  }
}
]);

Grouping with an _id of null means it will accumulate into a single object.

Documentation:

• $subtract
• $sum
• $group
• $unwind

Answer 2

If the arithmetic operations are calculated between two select documents, there might be a way to aggregate what you need. However, given that you mention you will query with the array [1, 4, 1, 1], I think it is unlikely for it to be possible without some javascript/application level query. I have a crude way of doing it, but it'll return you an array instead of a document.

//input would be from query, output would be the result, of course.
var input = [1,4,1,1];
var output = [];
var count = 0;


//unwind the fingerprints
db.fingerprint.aggregate([
    {$unwind : "$fingerPrint" }
]).forEach(function(doc){
    id = doc._id.valueOf();
    output.push(doc.fingerPrint - input[count]);
    count++;
})

print(output);

Using sample from your discussion above, the result from this would be [1, 0, 0 1]

If you are going to do the arithmetic using two documents however, I might be able to aggregate them using query.