MongoDB aggregate

Question

This my document structure:

{  
"_id":ObjectId("548eb9fe8ad582f2f9976973"),
"RECNUM":"1008",
"ART_NR":"1014",
"ART_NRA":"10010700",
"ART_BEZ":{  },
"BES1":{  },
"markings":[  ],
"prices":[  
  {  
     "DATUM_VON":"2011-04-01",
     "DATUM_BIS":"",
     "MENG_BETR_BIS":"1.0000",
     "PROZ_BETR":"4.2000"
  },
  {  
     "DATUM_VON":"2011-04-01",
     "DATUM_BIS":"",
     "MENG_BETR_BIS":"250.0000",
     "PROZ_BETR":"3.5000"
  },
  {  
     "DATUM_VON":"2011-04-01",
     "DATUM_BIS":"",
     "MENG_BETR_BIS":"500.0000",
     "PROZ_BETR":"3.4000"
  },
  {  
     "DATUM_VON":"2011-04-01",
     "DATUM_BIS":"",
     "MENG_BETR_BIS":"1000.0000",
     "PROZ_BETR":"3.3000"
  },
  {  
     "DATUM_VON":"2011-04-01",
     "DATUM_BIS":"",
     "MENG_BETR_BIS":"2500.0000",
     "PROZ_BETR":"3.2000"
  },

  {  
     "DATUM_VON":"2012-09-01",
     "DATUM_BIS":"",
     "MENG_BETR_BIS":"1.0000",
     "PROZ_BETR":"5.4000"
  },
  {  
     "DATUM_VON":"2012-09-01",
     "DATUM_BIS":"",
     "MENG_BETR_BIS":"250.0000",
     "PROZ_BETR":"4.5000"
  },
  {  
     "DATUM_VON":"2012-09-01",
     "DATUM_BIS":"",
     "MENG_BETR_BIS":"500.0000",
     "PROZ_BETR":"4.3500"
  },
  {  
     "DATUM_VON":"2012-09-01",
     "DATUM_BIS":"",
     "MENG_BETR_BIS":"1000.0000",
     "PROZ_BETR":"4.2000"
  },
  {  
     "DATUM_VON":"2012-09-01",
     "DATUM_BIS":"",
     "MENG_BETR_BIS":"2500.0000",
     "PROZ_BETR":"4.1000"
  }

] }

I need to extract in the prices array the documents that contain the max date value. So in this case all the documents (4) that have 2012-09-01 as value.

I've tried this:

 db.products.aggregate([
   { $match: {RECNUM: '1008'}},
   {"$unwind": "$prices"},
   {$match: { "$max": "$prices.DATUM_VON"}}
 ]);

But this throws an error as I guess I cannot use the $max operator in the $match. How can I achieve this?

Answer 1

Whilst it was already mentioned that you really should change the "date" type there to an actual BSON Date rather than a string, at least it is a "lexical" string and therefore valid for a comparison.

You really want something a little more complex than what your original attempt was. Point is that you need to find the maximum value in the array for that field and then you need to filter the array content for that value. So basically this:

db.collection.aggregate([
    { "$match": {
        "RECNUM": "1008" 
    }},
    { "$unwind": "$prices" },
    { "$group": {
        "_id": "$_id",
        "RECUM": { "$first": "$RECNUM" },
        "prices": { "$push": "$prices" },
        "max_date": { 
            "$max":"$prices.DATUM_VON"
        }
    }},
    { "$unwind": "$prices" },
    { "$project": {
        "RECNUM": 1,
        "prices": 1,
        "matched": { 
            "$eq": [ "$max_date", "$prices.DATUM_VON" ]
        }
    }},
    { "$match": { "matched": true } },
    { "$group": {
        "_id": "$_id",
        "RECNUM": { "$first": "$RECNUM" },
        "prices": { "$push": "$prices" }
    }}
])

We can get a bit more fancy from MongoDB versions 2.6 and upwards as long as the array elements are unique:

db.collection.aggregate([
    { "$match": {
        "RECNUM": "1008" 
    }},
    { "$unwind": "$prices" },
    { "$group": {
        "_id": "$_id",
        "RECUM": { "$first": "$RECNUM" },
        "prices": { "$push": "$prices" },
        "max_date": { 
            "$max":"$prices.DATUM_VON"
        }
    }},
    { "$project": {
        "RECNUM": 1,
        "prices": {
            "$setDifference": [
                "$map": {
                     "input": "$prices",
                     "as": "p",
                     "in": {
                         "$cond": [
                             { "$eq": [ "$$p.DATNUM_VON", "$max_date" ] },
                             "$$p",
                             false
                         ]
                     }
                },
                false
            ]
        }
    }}
])

Looks a bit meatier, but of MongoDB 2.6 and greater it is generally a faster way to do this, and especially with larger arrays.

It's all in abbreviates notation for the fields you might require for your actual output, but your should get the general idea. The $first operator is you friend when grouping on certain values.