MongoDB sort documents by specified element in array

Question

I have a collection 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 collection 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?

Takis · Accepted Answer · 2022-04-09 16:32:12Z

2

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.

PlayMongo

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"]}])

edited Apr 9, 2022 at 16:32

answered Oct 28, 2021 at 17:42

Takis

8,7552 gold badges17 silver badges28 bronze badges

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

4 Comments

Han Van Pham Over a year ago

Thank you, bro! I love your way!

J.F. Over a year ago

I've upvoted because the effort and I like reduce way but this not sort by active. Check this example

Takis Over a year ago

yes it doesnt sorts the array, only the collection. question was confusing a bit, list meant the collection i think, i wasnt sure also. @JF solution sorts the array by active fist i think, if you need that use the other solution. Because the above solution doesnt change the array at all.

Han Van Pham Over a year ago

I'm sorry for making you guys confuse! Basically, I don't need to sort the array! I just want to sort the object based on the createdAt date of the active package only.

J.F. · Accepted Answer · 2021-10-28 17:47:13Z

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

Collectives™ on Stack Overflow

MongoDB sort documents by specified element in array

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related