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?

CodePudding user response：

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

CodePudding user response：

Here is a variation that sorts more granularly on author "not containing 6".

db.foo.aggregate([
    {$match: {shelf:'a'}}
    ,{$unwind: '$products'}
    ,{$addFields: {sortMarker: {$cond: [
    {$in: [6, '$products.ids']},
        "Z",  // THEN make sortMarker at the end                                 
        "A"   // ELSE make sortMarker at the start                               
    ]}
                  }}

    ,{$sort: {'author':1, 'sortMarker':1}}
]);

which given this input set:

{"products" : [
        {"name": "name3", "ids": [6, 7]},
        {"name": "name2", "ids": [4, 5]}
    ],
     "author" : "Homer",
     "shelf": "a" },

    {"products" : [
    {"name": "name1", "ids": [4, 5, 6]},
    {"name": "name4", "ids": [9]},
    {"name": "name7", "ids": [9,6]},
        {"name": "name7", "ids": [10]}

    ],
     "author" : "Dante",
     "shelf": "a"},

    { "products" : [
    {"name": "name1", "ids": [2]}
    ], "author" : "Dante",
      "shelf": "b"}

yields this result:

{
    "_id" : 1,
    "products" : {
        "name" : "name4",
        "ids" : [
            9
        ]
    },
    "author" : "Dante",
    "shelf" : "a",
    "sortMarker" : "A"
}
{
    "_id" : 1,
    "products" : {
        "name" : "name7",
        "ids" : [
            10
        ]
    },
    "author" : "Dante",
    "shelf" : "a",
    "sortMarker" : "A"
}
{
    "_id" : 1,
    "products" : {
        "name" : "name1",
        "ids" : [
            4,
            5,
            6
        ]
    },
    "author" : "Dante",
    "shelf" : "a",
    "sortMarker" : "Z"
}
{
    "_id" : 1,
    "products" : {
        "name" : "name7",
        "ids" : [
            9,
            6
        ]
    },
    "author" : "Dante",
    "shelf" : "a",
    "sortMarker" : "Z"
}
{
    "_id" : 0,
    "products" : {
        "name" : "name2",
        "ids" : [
            4,
            5
        ]
    },
    "author" : "Homer",
    "shelf" : "a",
    "sortMarker" : "A"
}
{
    "_id" : 0,
    "products" : {
        "name" : "name3",
        "ids" : [
            6,
            7
        ]
    },
    "author" : "Homer",
    "shelf" : "a",
    "sortMarker" : "Z"
}

Optionally, this stage can be added after the $sort:

    {$group: {_id: '$author', products: {$push: '$products'}}}

And this will bring the sorted "not containing 6 then containing 6" items together again as an array packaged by author; the $push retains the order. Note we need only need author in _id because the match was for one shelf. If more than one shelf is in the match, then we would need:

    {$group: {_id: {author:'$author',shelf:'$shelf'}, products: {$push: '$products'}}}