How to sort on a field of type array in MongoDB

user_7533957 0
Viewed 217

I am trying to write a query which will sort specific_dates array in ascending order and then sort on the basis of first date in the array in MongoDB.

I have following data:

/* 1 */
{
    "_id" : ObjectId("5f9c0dfaf88d0a318ad826b1"),
    "holiday_for" : "range",
    "specific_dates" : [ 
        "2020-10-30", 
        "2020-10-31", 
        "2020-11-01", 
        "2020-11-02", 
        "2020-11-03", 
        "2020-11-04", 
        "2020-11-05", 
        "2020-11-06", 
        "2020-11-07", 
        "2020-11-08", 
        "2020-11-09", 
        "2020-11-10", 
        "2020-11-11", 
        "2020-11-12", 
        "2020-11-13", 
        "2020-11-14", 
        "2020-11-15", 
        "2020-11-16", 
        "2020-11-17", 
        "2020-11-18", 
        "2020-11-19", 
        "2020-11-20", 
        "2020-11-21", 
        "2020-11-22", 
        "2020-11-23", 
        "2020-11-24", 
        "2020-11-25", 
        "2020-11-26", 
        "2020-11-27", 
        "2020-11-28", 
        "2020-11-29", 
        "2020-11-30", 
        "2020-12-01", 
        "2020-12-02", 
        "2020-12-03", 
        "2020-12-04", 
        "2020-12-05", 
        "2020-12-06", 
        "2020-12-07", 
        "2020-12-08", 
        "2020-12-09", 
        "2020-12-10", 
        "2020-12-11", 
        "2020-12-12", 
        "2020-12-13", 
        "2020-12-14", 
        "2020-12-15", 
        "2020-12-16", 
        "2020-12-17", 
        "2020-12-18", 
        "2020-12-19", 
        "2020-12-20", 
        "2020-12-21", 
        "2020-12-22", 
        "2020-12-23", 
        "2020-12-24", 
        "2020-12-25"
    ],
    "description" : "Add holiday",
    "location_id" : 0
}

/* 2 */
{
    "_id" : ObjectId("5fa2220abb17c6473c2f4368"),
    "holiday_for" : "specific",
    "specific_dates" : [ 
        "2020-10-31", 
        "2020-11-02", 
        "2020-10-30"
    ],
    "description" : "Add holiday",
    "location_id" : 0
}

/* 3 */
{
    "_id" : ObjectId("5fc7591a3b1b0175500d07ea"),
    "holiday_for" : "specific",
    "specific_dates" : [ 
        "2020-12-05", 
        "2020-12-02", 
        "2020-12-01"
    ],
    "location_id" : 0
}

I need to sort these documents on the basis of first date in specific_dates array:

db.getCollection('holidays').aggregate([
{$unwind: "$specific_dates"},
{$sort: {"specific_dates": -1}},
{$group: {
    "_id": "$_id", 
    "holiday_for": {$first: "$holiday_for"}, 
    "description": {$first: "$description"},
    "location_id": {$first: "$location_id"},
    "specific_dates": {$push: "$specific_dates"}}},
])

But this is giving following unexpected result. Can someone guide me how can I perform this ?

mongodb
2 Answers

No need to $unwind you can use $first to get first element from array,

db.collection.aggregate([
  { $addFields: { first_date: { $first: "$specific_dates" } } },
  { $sort: { first_date: -1 } }
])

Playground

Second option you can use $arrayElemAt,

db.collection.aggregate([
  { $addFields: { first_date: { $arrayElemAt: ["$specific_dates", 0] } } },
  { $sort: { first_date: -1 } }
])

Playground

Third option as per your comment, to sort that array first,

  • $reduce to iterate loop of specific_dates array that is sort by ascending order using $setUnion, $concatArrays first current object and second initial value, it will change the order of array to descending order
  • next 2 stages remain same
db.collection.aggregate([
  {
    $addFields: {
      specific_dates: {
        $reduce: {
          input: { $setUnion: "$specific_dates" },
          initialValue: [],
          in: { $concatArrays: [["$$this"], "$$value"] }
        }
      }
    }
  },
  { $addFields: { first_date: { $arrayElemAt: ["$specific_dates", 0] } } },
  { $sort: { first_date: -1 } }
])

Playground

user_89871280

To sort by the array fields (first sort array of dates in ascending order then pick the first date to apply sort)


db.getCollection('holidays').aggregate([
    {
        $project: {
            "_id": 1,
            "holiday_for": 1,
            "description": 1,
            "location_id": 1,
            "specific_dates": 1,
            "first_date": {$min: "$specific_dates"}
        }
    },
    {
        $sort: {
            first_date: -1
        }
    }
])
user_75339570
Related