CODEWITHSUNDEEP
javascriptmongodbsortingmongodb-queryaggregation-framework
mikeysee
mikeysee

Reputation: 1763

How to sort by the difference in array contents in mongodb?

Suppose I have the following documents in my collection:

var data = [
         { myArray: [ { a:1 }, { a:45 }, { a:46 } ] },
         { myArray: [ { a:3 } ] },
         { myArray: [ { a:12 }, { a:3 } ] },
         { myArray: [ { a:14 }, { a:33 }, { a:66 } ] }
     ]

I want to sort them by the difference in the last two "a" values in the "myArray".

So the algorithm should be:

1) reject any documents where myArray length < 2

var result = [
         { myArray: [ { a:1 }, { a:45 }, { a:46 } ] },
         { myArray: [ { a:12 }, { a:3 } ] },
         { myArray: [ { a:14 }, { a:33 }, { a:66 } ] }
     ]

2) find the difference in the last two "a" values

var result = [
         { myArray: [ { a:1 }, { a:45 }, { a:46 } ], diff: 1 },
         { myArray: [ { a:12 }, { a:3 } ], diff: -9 },
         { myArray: [ { a:14 }, { a:33 }, { a:66 } ], diff: 33 }
     ]

3) sort by "diff"

var result = [
         { myArray: [ { a:14 }, { a:33 }, { a:66 } ], diff: 33 },
         { myArray: [ { a:1 }, { a:45 }, { a:46 } ], diff: 1 },
         { myArray: [ { a:12 }, { a:3 } ], diff: -9 }             
     ]

4) return the result

Upvotes: 2

Views: 661

Answers (1)

Sede
Sede

Reputation: 61225

The best way to do this is using the .aggregate() if your MongoDB server version is 3.2 or newer. The first stage in the pipeline is use the $match aggregation pipeline operator to filter out all those documents where "myArray" length is less than 2. This operation also reduce the number of documents to be processed down the pipeline. The next stage is the $project stage where you use the $arrayElemAt operator which returns element at a specified index, here the last two elements in the array using index -1 and index-2. Of course the $let operator here is used to assign those values to a variables which are then used in the $subtract expression to return the difference. The last stage is the $sort aggregation pipeline stage where you sort your documents.

db.collection.aggregate([ 
    { "$match": { "myArray.1": { "$exists": true } } }, 
    { "$project": { 
        "myArray": "$myArray", 
        "diff": { 
            "$let": { 
                "vars": { 
                    "first": { "$arrayElemAt": [ "$myArray", -2 ] }, 
                    "last": { "$arrayElemAt": [ "$myArray", -1 ] } 
                }, 
                "in": { "$subtract": [ "$$last.a", "$$first.a" ] } 
            } 
        } 
    }}, 
    { "$sort": { "diff": -1 } }
])

Which produces something like this:

{ "myArray" : [ { "a" : 14 }, { "a" : 33 }, { "a" : 66 } ], "diff" : 33 }
{ "myArray" : [ { "a" : 1 }, { "a" : 45 }, { "a" : 46 } ], "diff" : 1 }
{ "myArray" : [ { "a" : 12 }, { "a" : 3 } ], "diff" : -9 }

I don't thing there is a way to do this but you can always do this client-side using Array.sort

db.collection.find({ "myArray.1": { "$exists": true } }).toArray().sort(
    function(a, b) { 
        return a === b ? 0 :( a < b ? -1 : 1 ); 
    }
)

This return the same result as the query using the aggregation framework except that the "diff" field is absent. Also it worth mentioned that the aggregation solution is much faster than this.

Upvotes: 3

Related Questions