MongoDB document nested document filtering

Question

I'm fighting with MongoDb

I have a collection of documents with single document structured as follows

{
    "_id": { "$oid": "588a931d5c98fe0f3f84d93f" },
    "name": "Michele",
    "type": "F",
    "category": "G",
    "meta":{
        "code": "113835667",
        "updated": {"$date": "2017-02-07T00:00:00.000Z"},
        "since": {"$date": "2013-11-07T23:00:00.000Z"}
    },
    "data": [
        {
          "date": {"$date": "2013-11-07T23:00:00.000Z"},
          "close": 12.23
        }
        ... // removed 
        {
          "date": {"$date": "2017-02-07T00:00:00.000Z"},
          "close": 15.22
        }
    ]
}

What i need to achieve is returning the document with matching _id but filter out from the data array the documents with date property not inside a specified time range.

this is what I've attempted since now

 let id;    //[DocumentId]
 let from;  //[Date]
 let to;    //[Date]
 collection.aggregate([
            { $match: { _id: { $eq: id } } },
            { $unwind: '$data' },
            { $match: { 'data.date': { $gte: from, $lte: to } } },
            { $group: { _id: '$_id', 
                        data: { $push: { date:'$data.date', close: '$data.close' } } } }
        ], ...);

the problem with this approach is that the document I return only contains _id and data properties [data filtering result is ok] while I need to return the full set of available properties.

suggestions are greatly appreciated!

What is your MongoDB server version and are you running the aggregate operation from mongo shell? — chridam, Feb 08 '17 at 10:56
@chridam it says mongod version: 3.2.11and no I'm using tha nodejs driver "mongodb": "^2.2.6", — Gavello, Feb 09 '17 at 10:32
Possible duplicate of [Retrieve only the queried element in an object array in MongoDB collection](http://stackoverflow.com/questions/3985214/retrieve-only-the-queried-element-in-an-object-array-in-mongodb-collection) — s7vr, Feb 10 '17 at 01:51

score 2 · Accepted Answer · answered Feb 10 '17 at 06:26

If you could upgrade to Mongo 3.4 (the latest stable release) it could be done nicely:

db.collection.aggregate([
        //Pre-filter to have data arrays with at least one matching date
 {$match: {_id: id, 'data.date': {$gte: from, $lte: to}}},
        //Filter the items array
 {
  $addFields: {
   'items': {
    $filter: {
     input: '$data', as: 'item', cond: {
      $and: [
       {$gte: ["$$item.date", from]},
       {$lte: ["$$item.date", to]}
      ]
     }
    }
   }
  }
 }
]);

If you have to keep mongo 3.2 the only thing I can think of is to use $ROOT like this:

db.collection.aggregate([
 {$match: {_id: id, 'data.date': {$gte: from, $lte: to}}},
 {
  $project: {
   'filteredData': {
    $filter: {
     input: '$data', as: 'item', cond: {
      $and: [
       {$gte: ["$$item.date", from]},
       {$lte: ["$$item.date", to]}
      ]
     }
    }
   },
   'originalDocument': '$$ROOT'
  }
 }
]);

The resulting objects will have originalDocument and filteredData as properties, and you'll need to process them in your server code (one loop in fact).

MongoDB document nested document filtering

1 Answers1