I have a list like that:

[
  { 
    student: "a", 
    package: [ 
      {name: "one", createdAt: "2021-10-12T00:00:00", active: true}, 
      {name: "two", createdAt: "2021-10-13T00:00:00", active: false}, 
      {name: "three", createdAt: "2021-10-14T00:00:00", active: false} 
    ] 
  },
  { 
    student: "b", 
    package: [ 
      {name: "one", createdAt: "2021-10-16T00:00:00", active: true}, 
      {name: "two", createdAt: "2021-10-17T00:00:00", active: false}, 
      {name: "three", createdAt: "2021-10-18T00:00:00", active: false} 
    ] 
  },
  { 
    student: "c", 
    package: [ 
      {name: "one", createdAt: "2021-10-10T00:00:00", active: true}, 
      {name: "two", createdAt: "2021-10-17T00:00:00", active: false}, 
      {name: "three", createdAt: "2021-10-18T00:00:00", active: false} 
    ] 
  }
]

I have no idea how can I do a query (Mongodb) to sort this list based on the createdAt with active: true in the package array?

The expectation looks like this:

[
  { 
    student: "c", 
    package: [ 
      {name: "one", createdAt: "2021-10-10T00:00:00", active: true}, 
      ...
    ] 
  },
  { 
    student: "a", 
    package: [ 
      {name: "one", createdAt: "2021-10-12T00:00:00", active: true},
      ... 
    ] 
  },
  { 
    student: "b", 
    package: [ 
      {name: "one", createdAt: "2021-10-16T00:00:00", active: true}, 
      ...
    ] 
  },
]

Could anyone help me with this? The idea comes up to my mind just to use the code to sort it, but is it possible to use a query MongoDB?

CodePudding user response：

Query

• creates a sort-key for each document, this is the latest date of the active package members (the $reduce does, this keeping the max date)
• sort by it
• unset to remove this extra key

*for descended or ascedent, you can chage the $gt with $lt and the sort 1, with sort -1. depending on what you need. If you use $lt replace "0" also with a max string like "9". Or if you have real dates, with a min or max date.

Test code here

aggregate(
[{"$set": 
   {"sort-key": 
     {"$reduce": 
       {"input": "$package",
        "initialValue": "0",
         "in": 
         {"$cond": 
           [{"$and": 
            ["$$this.active", {"$gt": ["$$this.createdAt", "$$value"]}]},
             "$$this.createdAt", "$$value"]}}}}},
 {"$sort": {"sort-key": 1}},
 {"$unset": ["sort-key"]}])

CodePudding user response：

You can use this aggregation query:

• First $unwind to deconstruct the array ang get each value.
• Then $sort by active.
• $group to get the initial data but sorted.
• And last $sort again by createdAt.

db.collection.aggregate([
  {
    "$unwind": "$package"
  },
  {
    "$set": {
      "package.createdAt": {
        "$toDate": "$package.createdAt"
      }
    }
  },
  {
    "$sort": {
      "package.active": -1
    }
  },
  {
    "$group": {
      "_id": "$_id",
      "student": {
        "$first": "$student"
      },
      "package": {
        "$push": "$package"
      }
    }
  },
  {
    "$sort": {
      "package.createdAt": 1
    }
  }
])

Example here

Also, to do the sorting, is better if createdAt is a Date field, otherwise you should parse to date. Like this example