Question 175430 in Mongodb Question posted in
The official documentation can be found here.

Can mongodb sort a collection using a static value and other field value desending?

My colection is a list of products with the following structure

    {
      _id
      name
      status  // enum ['active','sold','reserved','pending']
      .
      .
      createdAt
    }

What I need is a sort feature along with the status value passed to the system, I ll give an example below

case: If status = ‘active’ , Then result should sort as given below

All products with status active should come first (Active list itself should be in createdAt desc order)

Remaining products should list createdAt in desc order

Follow the same with all other status value.

If status is empty then list everything in desc order

What I tried is given below

    db.products.aggregate([
         {
            $project: {
            "_id":1,
            "name":1,
            "status":1,
            "createdAt":1
            }
         },
         {$sort:{status:"active", createdAt:-1}}, // Error
         {$sort:{status:1, createdAt:-1}}, // Result work but not as expected
    ])

Any help would be much appreciated

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I managed to get one solution which is listed below, But I am open to any better solution if exists

        db.products.aggregate([
         {
            $project: {
            "_id":1,
            "name":1,
            "status":1,
            "createdAt":1,
            "timeStamp": {
             "$switch": {
                "branches": [
                    { "case": { "$eq": [ "$status", "pending" ] }, "then": 99999999999999 },
                ],
                "default": {"$toLong": "$createdAt"}
                 }
              },
                "createdTime": {
                             "$toLong": "$createdAt"
                           }
              }
         },
         {$sort:{timeStamp:-1, createdTime:-1}}
    ])

  2. 0

    Another option is to create a separate field which specifies which status value to come first

    1. Add a field sortBy which assigns 0 if it is has the status value that should come first else assign 1
    2. Do the usual sorting sortBy ascending and createdAt descending
    3. Remove the newly added field from the output result using project
    const status = 'active'

db.products.aggregate([
  { $addFields: { sortBy: { $cond: [ { $eq: [ "$status", status ] }, 0, 1 ] } } },
  { $sort: { sortBy: 1, createdAt: -1 } },
  { $project: { sortBy: 0 } }
])

    playground

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search