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

Fetch records from mongodb by sorting based on a field and add a specific record on top of the results

I have below set of user records, I need the results in such a way that logged in user(ObjectId("653222c20489664953bbaf1a") and status: Active) should come first then status: Pending users and finally rest of the status: Active users. How do I achieve this using mongo query?

{
    "_id" : ObjectId("654b7348e704b8abba8af17e"),
    "name": "",
    "email" : "",
    "status" : "Active"
},

{
    "_id" : ObjectId("654b6c662f38d9e087086193"),
    "name": "",
    "email" : "",
    "status" : "Pending"
},
{
    "_id" : ObjectId("654b7fbae8392eddee2d1a31"),
    "name": "",
    "email" : "",
    "status" : "Pending"
},
{
    "_id" : ObjectId("653222c20489664953bbaf1a"),
    "name": "",
    "email" : "",
    "status" : "Active"
},
{
    "_id" : ObjectId("653269653bc13f1b96c6c37d"),
    "name": "",
    "email" : "",
    "status" : "Active"
}
Tags:

2

Answers


  1. 0

    Solution 1:

    1. $facet – Allow to run multiple pipelines in a single query. Each pipeline contains the filter criteria that return different datasets.

    2. $project – Decorate the output document. Combine the datasets from the previous stage into a single array.

    3. $unwind – Deconstruct the users array.

    4. $replaceWith – Replace the input document with users document.

    db.collection.aggregate([
  {
    $facet: {
      loginUsers: [
        {
          $match: {
            _id: ObjectId("653222c20489664953bbaf1a"),
            status: "Active"
          }
        }
      ],
      pendingUsers: [
        {
          $match: {
            status: "Pending"
          }
        }
      ],
      activeUsers: [
        {
          $match: {
            $expr: {
              $and: [
                {
                  $eq: [
                    "$status",
                    "Active"
                  ]
                },
                {
                  $ne: [
                    "$_id",
                    ObjectId("653222c20489664953bbaf1a")
                  ]
                }
              ]
            }
          }
        }
      ]
    }
  },
  {
    $project: {
      _id: 0,
      users: {
        $concatArrays: [
          "$loginUsers",
          "$pendingUsers",
          "$activeUsers"
        ]
      }
    }
  },
  {
    $unwind: "$users"
  },
  {
    $replaceWith: "$users"
  }
])

    Demo Solution 1 @ Mongo Playground

    Solution 2:

    1. $set – Add rank field to assign the value based on criteria via $switch.

    2. $match – Remove the document with rank: -1 in case there is the document with the status other than "Pending" and "Active".

    3. $sort – Sort by the rank field ascending.

    4. $unset – Remove the rank field.

    db.collection.aggregate([
  {
    $set: {
      rank: {
        $switch: {
          branches: [
            {
              case: {
                $and: [
                  {
                    $eq: [
                      "$_id",
                      ObjectId("653222c20489664953bbaf1a")
                    ]
                  },
                  {
                    $eq: [
                      "$status",
                      "Active"
                    ]
                  }
                ]
              },
              then: 0
            },
            {
              case: {
                $eq: [
                  "$status",
                  "Pending"
                ]
              },
              then: 1
            },
            {
              case: {
                $and: [
                  {
                    $ne: [
                      "$_id",
                      ObjectId("653222c20489664953bbaf1a")
                    ]
                  },
                  {
                    $eq: [
                      "$status",
                      "Active"
                    ]
                  }
                ]
              },
              then: 2
            }
          ],
          default: -1
        }
      }
    }
  },
  {
    $match: {
      rank: {
        $ne: -1
      }
    }
  },
  {
    $sort: {
      rank: 1
    }
  },
  {
    $unset: "rank"
  }
])

    Demo Solution 2 @ Mongo Playground

    Login or Signup to reply.
  2. 0

    Add "sorting key" fields to your records, one for the exact match and one for the account status. Since 0 < 1, set those field values accordingly.

    db.collection.aggregate(
  [
    {
      $addFields: {
        id_you_are_looking_for: {
          $cond: {
            if: { $eq: ["$_id", ObjectId("653222c20489664953bbaf1a")] },
            then: 0,  // exact match gets lowest value
            else: 1,
          },
        },
        acc_status_sort: {
          $cond: {
            if: { $eq: ["$status", "Pending"] },
            then: 0,  // status pending gets lowest value
            else: 1,
          },
        },
      },
    },
    {
      $sort: {
        id_you_are_looking_for: 1,  // first, ascending order sort on exact match
        acc_status_sort: 1,  // then, ascending order sort on status
      },
    },
    // remove the extra fields added
    // comment out this part to see the sorting fields in results
    {
      $project: {
        id_you_are_looking_for: 0,
        acc_status_sort: 0,
      },
    },
  ]
)

    Mongo Playground, with the sort key fields present for clarity.

    Result:

    [
  {
    "_id": ObjectId("653222c20489664953bbaf1a"),
    "acc_status_sort": 1,
    "id_you_are_looking_for": 0,
    "status": "Active"
  },
  {
    "_id": ObjectId("654b6c662f38d9e087086193"),
    "acc_status_sort": 0,
    "id_you_are_looking_for": 1,
    "status": "Pending"
  },
  {
    "_id": ObjectId("654b7fbae8392eddee2d1a31"),
    "acc_status_sort": 0,
    "id_you_are_looking_for": 1,
    "status": "Pending"
  },
  {
    "_id": ObjectId("654b7348e704b8abba8af17e"),
    "acc_status_sort": 1,
    "id_you_are_looking_for": 1,
    "status": "Active"
  },
  {
    "_id": ObjectId("653269653bc13f1b96c6c37d"),
    "acc_status_sort": 1,
    "id_you_are_looking_for": 1,
    "status": "Active"
  }
]

    As an alternative, you could also nest the else condition in the first $cond to use values 2 & 3 for account status and then sort on only one field.

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

Back To Top
Search