I have collection like this:

{ 
    "_id" : 13965 ,
    "name" : "p 1",
    "sort_order" : 0,
    "quantity" : 5 
}
{ 
    "_id" : 8000 ,
    "name" : "p 4",
    "sort_order" : 500,
    "quantity" : -10
}
{ 
    "_id" : 13980 ,
    "name" : "p 2",
    "sort_order" : 50,
    "quantity" : 2
}
{ 
    "_id" : 9753 ,
    "name" : "p 5",
    "sort_order" : 0,
    "quantity" : 0
}
{ 
    "_id" : 2000 ,
    "name" : "p 3",
    "sort_order" : 6,
    "quantity" : 1
}
{ 
    "_id" : 65230,
    "name" : "p 6",
    "sort_order" : 20,
    "quantity" : 0
}

after sort, i want it to look like this :

{ 
    "_id" : 13980 ,
    "name" : "p 2",
    "sort_order" : 50,
    "quantity" : 2
}

{ 
    "_id" : 2000 ,
    "name" : "p 3",
    "sort_order" : 6,
    "quantity" : 1
}

{ 
    "_id" : 13965 ,
    "name" : "p 1",
    "sort_order" : 0,
    "quantity" : 5 
}

{ 
    "_id" : 8000 ,
    "name" : "p 4",
    "sort_order" : 500,
    "quantity" : -10
}

{ 
    "_id" : 65230,
    "name" : "p 6",
    "sort_order" : 20,
    "quantity" : 0
}

{ 
    "_id" : 9753 ,
    "name" : "p 5",
    "sort_order" : 0,
    "quantity" : 0
}

I want all the cases that quantity has zero or below zero to be at the end of the list and the rest to sort by sort_order. This Mysql query work:

SELECT product_id,quantity,sort_order FROM product ORDER by quantity <=0 ASC , sort_order DESC

but i need in mongoDB :))

CodePudding user response：

Try this one:

db.collection.aggregate([
  {
    $addFields: {
      sortQuantity: { $cond: [ { $lte: ["$quantity", 0 ] }, 0, 1 ] }
    }
  },
  {
    $sort: {
      sortQuantity: 1,
      sort_order: -1
    }
  },
  { $unset: "sortQuantity" }
])

Mongo Playground

CodePudding user response：

You can use

• $facet to categorize incoming data into quantity>0 and quantity <=0
• $sort to sort as you expected
• $concatArray to concat both array into one array
• $unwind to deconstruct the array
• $replaceRoot to make the deconstructed variable as root

here is the ocde

db.collection.aggregate([
  {
    "$facet": {
      "lteZero": [
        {
          $match: {
            $expr: {
              $lte: [ "$quantity", 0 ]
            }
          }
        },
        { $sort: { sort_order: -1, quantity: 1 } }
      ],
      "gtZero": [
        {
          $match: {
            $expr: {
              $gt: [ "$quantity", 0 ]
            }
          }
        },
        { $sort: { sort_order: -1, quantity: 1 } }
      ]
    }
  },
  {
    "$project": {
      combined: {
        "$concatArrays": [ "$gtZero", "$lteZero" ]
      }
    }
  },
  { "$unwind": "$combined" },
  { "$replaceRoot": { "newRoot": "$combined" } }
])

working Mongo playground