How to filter array records without unwind and sort stage?

Working with Data
,
1

Hi All,

Consider this as a sample document, and the date purchased value can be in any order.

   {
    "_id" : ObjectId("60181d30bbc953f2c6f3cece"),
    "ProductDetails" : [ 
        {
            "name" : "abc",
            "datepurchased" : ISODate("2018-07-29T05:15:21.594Z")
        }, 
        {
            "name" : "xxx",
            "datepurchased" : ISODate("2021-07-29T05:15:21.594Z")
        }, 
        {
            "name" : "abc",
            "datepurchased" : ISODate("2019-07-29T05:15:21.594Z")
        }
    ]
}

How to filter the records in the array field by name = “abc” and get one recent product by date purchased and how to achieve this without including $unwind and $sort stage?

Let me know this can be possible in the mongo db 4.2 version.

2

Hello @Sudhesh_Gnanasekaran,

Select first matching object/element after Filtering from ProductDetails array:

  • Filter ProductDetails array by name condition using $filter operator
  • $arrayElemAt return element/object from 0 index after filtering
  {
    $addFields: {
      ProductDetails: {
        $arrayElemAt: [
          {
            $filter: {
              input: "$ProductDetails",
              cond: { $eq: ["$$this.name", "abc"] }
            }
          },
          0
        ]
      }
    }
  }

Select recent matching element/object on the base of date after Filtering from ProductDetails array:

First Stage:

  • Filter ProductDetails array by name condition using $filter operator,
  {
    $addFields: {
      ProductDetails: {
        $filter: {
          input: "$ProductDetails",
          cond: { $eq: ["$$this.name", "abc"] }
        }
      }
    }
  }

Second Stage:

  • Get the maximum date from the filtered array { $max: "$ProductDetails.datepurchased" }
  • Get the index of returned maximum date in ProductDetails array using $indexOfArray
  • Get element from ProductDetails array of returned index from $indexOfArray using $arrayElemAt 
  {
    $addFields: {
      ProductDetails: {
        $arrayElemAt: [
          "$ProductDetails",
          {
            $indexOfArray: [
              "$ProductDetails.datepurchased",
              { $max: "$ProductDetails.datepurchased" }
            ]
          }
        ]
      }
    }
  }

You can do it using single stage by $let,

  {
    $addFields: {
      ProductDetails: {
        $let: {
          vars: {
            p: {
              $filter: {
                input: "$ProductDetails",
                cond: { $eq: ["$$this.name", "abc"] }
              }
            }
          },
          in: {
            $arrayElemAt: [
              "$$p",
              { 
                $indexOfArray: [
                  "$$p.datepurchased", 
                  { $max: "$$p.datepurchased" }
                ] 
              }
            ]
          }
        }
      }
    }
  }
1 Like
3

@turivishal Thank you query working perfectly, and how about the performance compared with the unwinding and Sort stage?

4

Depends on data in your collection and number of elements in array, You may want to take a look a the explain() of the query, this will tell you exactly where mongodb is spending time, compare both queries performance.

5

@turivishal Thank you.

6

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.