Mongo ranking results

Question

I have a collection like

db.books.insertMany([
  {"products" : [{"name": "name1", "ids": [4, 5, 6]}], "author" : "Dante", "shelf": "a" },
  { "products" : [{"name": "name1", "ids": [4, 5]}], "author" : "Homer", "shelf": "a" },
  { "products" : [{"name": "name1", "ids": [2]}], "author" : "Dante", "shelf": "b" },
])

and I want to retrieve all documents where "shelf" is 'a' and sort by 2 conditions: 1 - by Author 2 - documents where products.ids not contains 6 should be the first.

Could anyone help?

Answer 1

You can try this query:

• First $match the shelf value with "a".
• Then create an auxiliar value where will be true if 6 not exists into products.ids, otherwise false.
• Then $sort by values you want.
• And use $project to remove the auxiliar value.

db.collection.aggregate([
  {
    "$match": {"shelf": "a"}
  },
  {
    "$set": {
      "rank": {
        "$eq": [
          {
            "$filter": {
              "input": "$products",
              "cond": {"$in": [6,"$$this.ids"]}
            }
          },[]
        ]
      }
    }
  },
  {
    "$sort": {
      "rank": -1,
      "author": 1
    }
  },
  {
    "$project": {"rank": 0}
  }
])

Example here