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
– chridam, Commented Feb 8, 2017 at 10:56
@chridam it says mongod version: 3.2.11and no I'm using tha nodejs driver "mongodb": "^2.2.6", — Gavello
– Gavello, Commented Feb 9, 2017 at 10:32
Possible duplicate of Retrieve only the queried element in an object array in MongoDB collection — s7vr
– s7vr, Commented Feb 10, 2017 at 1:51

Antonio Narkevich · Accepted Answer · 2017-02-10 06:26:44Z

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).

Collectives™ on Stack Overflow

MongoDB document nested document filtering

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related