mongodb conditional random sort

Question

I have mongo collection like this,

  [{
    id: 1,
    "liked": false,
    "opened": 2022-10-01T00:00:00.000+00:00
  },
  {
    id: 2,
    "liked": true,
    "opened": 2022-10-02T00:00:00.000+00:00
  },
  {
    id: 3,
    "liked": false,
    "opened": 2022-08-03T00:00:00.000+00:00
  },
  {
    id: 4,
    "liked": true,
    "opened": 2022-10-04T00:00:00.000+00:00
  }]

here liked is a boolean, and opened is date time, I want to sort like below

Show records with liked=true first.
- Randomize the records inside it
Show records where opened is less than 30 days old
- Randomize the records inside it
Show records where opened is over 30 days ago
- Randomize the order

one sample output could be(id 2 & 4 are randomly ordered, 1 & 3 always be in the below order because of sorting rule 2 & 3 mentioned above)

  [{
    id: 2,
    "liked": true,
    "opened": 2022-10-02T00:00:00.000+00:00
  },
  {
    id: 4,
    "liked": true,
    "opened": 2022-10-04T00:00:00.000+00:00
  },{
    id: 1,
    "liked": false,
    "opened": 2022-10-01T00:00:00.000+00:00
  },
  {
    id: 3,
    "liked": false,
    "opened": 2022-08-03T00:00:00.000+00:00
  }]

I can sort like db.collection.find().sort({liked:-1,opened:1}) and then inside my code i can shuffle the records by creating 3 groups(liked=true, opened < 30 days and opened >30 days), however i want to know if i can achieve this somehow with mongo query itself.

ray · Accepted Answer · 2022-10-03 16:14:38Z

2

You can create an auxiliary field sortOrder with $switch to control your order of groups. Then use $rand to create random orderings within groups.

db.collection.aggregate([
  {
    "$addFields": {
      "sortOrder": {
        "$switch": {
          "branches": [
            {
              "case": "$liked",
              "then": 1
            },
            {
              "case": {
                $lt: [
                  {
                    "$dateDiff": {
                      "startDate": "$opened",
                      "endDate": "$$NOW",
                      "unit": "day"
                    }
                  },
                  30
                ]
              },
              "then": 2
            }
          ],
          default: 3
        }
      },
      "rand": {
        "$rand": {}
      }
    }
  },
  {
    $sort: {
      sortOrder: 1,
      rand: 1
    }
  }
])

Here is the Mongo Playground for your reference.

answered Oct 3, 2022 at 16:14

ray

15.6k12 gold badges97 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

mahendra Over a year ago

a followup, is there a way to consistently keep the first record always first? rest should be randomly sorted .

ray Over a year ago

You can just manipulate the sortOrder to achieve that. To some extreme extent, you can simply set the sortOrder to 0 if the _id matches the first record's _id, in order to keep it at the top.

mahendra Over a year ago

cant really hardcode an id, because before this sort stage i have a filter stage and data keeps changing, based on user actions.

ray Over a year ago

I mean the idea is manipulating the sortOrder. It depends on your actual use case to see whether it is feasible or not.

Collectives™ on Stack Overflow

mongodb conditional random sort

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related