MongoDB Filter Query on Array of Dates Returns empty array

user_12611260 0
Viewed 600

I have a MongoDB Document contains information about a product and it's variations, example of the structure is given below.

{
    "site_id": 3,
    "status": 2,
    "tags": [],
    "title": "WORTHBUY Bento Lunch Box for Kids, 2 Compartments Stainless Steel Square Lunch Box with Portable Cutlery, Portion Control Food Storage Container Leakproof, BPA Free(Pink)",
    "total_sold_count": 3,
    "amount_of_variations": 3,
    "variations": [{
            "price": 9.66,
            "shipping_price": 0,
            "quantity": 0,
            "sold_count": 1,
            "sold_dates": [
                ISODate("2020-03-03T17:00:00.000-07:00")
            ]
        },
        {
            "price": 18.42,
            "shipping_price": 0,
            "quantity": 0,
            "sold_count": 1,
            "sold_dates": [
                ISODate("2020-03-03T17:00:00.000-07:00")
            ]
        },
        {
            "price": 18.42,
            "shipping_price": 0,
            "quantity": 0,
            "sold_count": 1,
            "sold_dates": [
                ISODate("2020-03-03T17:00:00.000-07:00"),
                ISODate("2020-03-09T20:30:56.000-06:00")
            ]
        }
    ]
}

I'm trying to get all the variations that were sold after a specific date. To accomplish this I used the following mongo aggregation query but in the results, I get an empty variations list. 

db.products.aggregate([{
    $match: {
        "variations.sold_dates": {
            $gt: ISODate("2020-03-05 00:00:00")
        }
    }
}, {
    $addFields: {
        "variations": {
            $filter: {
                input: "$variations",
                as: "variations",
                cond: {
                    $and: [{
                        $gt: ["$$variations.sold_dates", ISODate("2020-03-05 00:00:00")]
                    }]
                }
            }
        }
    }
}])

This query worked for all other fields I tried except this list of date. Any Idea what I'm doing wrong?

aggregation-framework mongodb-query mongodb
2 Answers

Since variations.sold_dates is an array of dates, You can not compare a given date is greater than sold_dates. So you need to iterate on each element of variations.sold_dates array & check for greater than while iteration, As $filter returns an array of filter elements then you can check size of that array > 0 to add variations objects to variations array in $addFields. Try this query :

db.collection.aggregate([
  {
    $match: {
      "variations.sold_dates": {
        $gt: ISODate("2020-03-05T00:00:00Z")
      }
    }
  },
  {
    $addFields: {
      "variations": {
        $filter: {
          input: "$variations",
          as: "variation",
          cond: {
            $gt: [
              {
                $size: {
                  $filter: {
                    input: "$$variation.sold_dates",
                    cond: {
                      $gt: [
                        "$$this",
                        ISODate("2020-03-05T00:00:00.000Z")
                      ]
                    }
                  }
                }
              },
              0
            ]
          }
        }
      }
    }
  }
])

Test : MongoDB-Playground

user_72376130

Another solution is this one:

db.collection.aggregate([
   {
      $set: {
         "variations": {
            $filter: {
               input: "$variations",
               as: "variation",
               cond: {
                  $anyElementTrue: {
                     $map: {
                        input: "$$variation.sold_dates",
                        in: { $gt: ["$$this", ISODate("2020-03-05T00:00:00.000Z")] }
                     }
                  }
               }
            }
         }
      }
   }
])

Depending on your requirements you may replace $anyElementTrue with $allElementsTrue

user_30272660
Related