Get matched embedded document(s) from array

Question

I've got a lot of documents using the following structure in MongoDB:

{
    "_id" : ObjectId("..."),
    "plant" : "XY_4711",
    "hour" : 1473321600,
    "units" : [
        {
            "_id" : ObjectId("..."),
            "unit_id" : 10951,
            "values" : [
                {
                    "quarter" : 1473321600,
                    "value" : 395,
                },
                {
                    "quarter" : 1473322500,
                    "value" : 402,
                },
                {
                    "quarter" : 1473323400,
                    "value" : 406,
                },
                {
                    "quarter" : 1473324300,
                    "value" : 410,
                }
            ]
        }
    ]
}

Now I need to find all embedded document values where the quarter is between some given timestamps (eg: { $gte: 1473324300, $lte: 1473328800 }).

I've only got the unit_id and the quarter timestamp from/to for filtering the documents. And I only need the quarter and value grouped and ordered by unit.

I'm new in MongoDB and read something about find() and aggregate(). But I don't know how to do it. MongoDB 3.0 is installed on the server.

Finally I've got it:

I simply have to take apart each array, filtering out the things I don't need and put it back together:

db.collection.aggregate([ 
    {$match : {$and : [{"units.values.quarter" : {$gte : 1473324300}}, {"units.values.quarter" : {$lte : 1473328800 }}]}}, 
    {$unwind: "$units"}, 
    {$unwind: "$units.values"}, 
    {$match : {$and : [{"units.values.quarter" : {$gte : 1473324300}}, {"units.values.quarter" : {$lte : 1473328800 }}]}}, 
    {$project: {"units": {values: {quarter: 1, "value": 1}, unit_id: 1}}}, 
    {$group: {"_id": "$units.unit_id", "quarter_values": {$push: "$units.values"}}} ,
    {$sort: {"_id": 1}}
])

Will give:

{
    "_id" : 10951,
    "quarter_values" : [
        {
            "quarter" : 1473324300,
            "value" : 410
        },
        {
            "quarter" : 1473325200,
            "value" : 412
        },
        {
            "quarter" : 1473326100,
            "value" : 412
        },
        {
            "quarter" : 1473327000,
            "value" : 411
        },
        {
            "quarter" : 1473327900,
            "value" : 408
        },
        {
            "quarter" : 1473328800,
            "value" : 403
        }
    ]
}

See: Return only matched sub-document elements within a nested array for a detailed description!

I think I have to switch to $map or $filter in the future. Thanks to notionquest for supporting my questions :)

To filter in subdocuments you can simply chain the keys with dots like this: `db..find({units.values.quarter: {$gte: 1473324300, $lte: 1473328800}})` this way you will get all the matching documents. To only get back your array values look at projection https://docs.mongodb.com/v3.2/reference/operator/projection/positional/. This will however not sort and group your embedded array. — Erik, Oct 10 '16 at 15:12

score 3 · Accepted Answer · answered Oct 10 '16 at 16:06

3

Please see the sample query below. I didn't exactly get your grouping requirement. However, with this sample query you should be able to change and get your desired output.

db.collection.aggregate([
    {$unwind : {path : "$units"}},
    {$match : {$and : [{"units.values.quarter" : {$gte : 1473324300}}, {"units.values.quarter" : {$lte : 1473328800 }}]}},
    {$project : {"units" : {values : {quarter : 1, "value" : 1},  unit_id : 1}}},
    {$group : { _id : "$units.unit_id", quarter_values : { $push :{ quarter : "$units.values.quarter", value : "$units.values.value"}}}},
    {$sort : {_id : 1 }}
]);

Sample output:-

{
    "_id" : 10951,
    "quarter_values" : [ 
        {
            "quarter" : [ 
                1473321600, 
                1473322500, 
                1473323400, 
                1473324300
            ],
            "value" : [ 
                395, 
                402, 
                406, 
                410
            ]
        }
    ]
}

answered Oct 10 '16 at 16:06

notionquest

37,595
6
111
105

That's exactly what I want! But one last question: I'd read the following documentation https://docs.mongodb.com/manual/reference/operator/aggregation/push/. In the first example the cursor results an well formatted array of items (named _itemsSold_ in the example). I'd like to have the same output (eg `{quarter: 1473321600, value: 395}, {quarter: 1473322500, value: 402}, ...`. Is this due to multi-embedded documents? – tmieruch Oct 11 '16 at 08:11
Change the group to {$group : { _id : "$units.unit_id", quarter_values : { $push : "$units.values"}}}, – notionquest Oct 11 '16 at 08:20
That's great! Thanks a lot! +1 – tmieruch Oct 11 '16 at 09:00
Another quick question: Why do I get all embedded documents (quarters): 1473321600..1473323400 are before 1473324300. They should be excluded in the cursor. I only want to get the quarters between the given timestamps. I tried to do on this way: `"units.values.quarter": {$gte: 1473324300, $lte: 1473328800}`. Is it because the other quarters matched $lte condition? – tmieruch Oct 11 '16 at 13:05
Yes, $match selects the whole document. The query requires additional filters after $project to remove the other unnecessary values. – notionquest Oct 11 '16 at 14:30
I tried to do it using $redact. But I will always get an error message. I tried it on this way: `db.forecast.aggregate([{$unwind: {path: "$units"}}, {$match: {$and: [{"units.values.quarter":{$gte: 1473324300}}, {"units.values.quarter":{$lte: 1473328800}}]}},{$project:{"units":{values:{quarter:1,"value":1},unit_id:1}}},{$redact:{$cond:{if:{"$units.values.quarter":{$lt: 1473324300}},then:"$$DESCEND",else: "$$PRUNE"}}},{$group:{_id:"$units.unit_id",quarter_values: {$push:"$units.values"}}},{$sort:{_id:1}},{$limit: 1}]).pretty()` – tmieruch Oct 12 '16 at 09:55
Do you know how to exclude embedded documents that doesn't match the filter? – tmieruch Oct 12 '16 at 09:55

Get matched embedded document(s) from array

Finally I've got it:

1 Answers1