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

1. Show records with liked=true first.
   • Randomize the records inside it
2. Show records where opened is less than 30 days old
   • Randomize the records inside it
3. 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.

CodePudding user response：

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.