MongoDB filtering by 3rd level embedded objects

Question

I am working with MongoDB 3+ version. I have a collection of documents ("doc") "doc" has list of "rows". Every row has list of "units". Unit has field "status".

I have to return document with rows, but units need to be included only with status "new".

{
    "_id" : ObjectId("5b7a7184e0e432207cc47612"),
    "_class" : "MyClass",
    "rows" : [ 
        {            
            "activity" : false,
            "units" : [ 
                {
                    "unitId" : "35",
                    "status" : "status",
                    "bookingDay" : {
                        "dateValue" : ISODate("2018-06-30T21:00:00.000Z")
                    }
                }, 
                {
                    "unitId" : "40",
                    "status" : "new",
                    "bookingDay" : {
                        "dateValue" : ISODate("2018-08-15T21:00:00.000Z")
                    }
                }
            ]
        }
    ],
    "mediaId" : NumberLong(4)
}

expected result - the first unit excluded:

{
    "_id" : ObjectId("5b7a7184e0e432207cc47612"),
    "_class" : "com.samsolutions.adbuybackend.model.booking.radio.BookingBoardRadio",
    "rows" : [ 
        {
            "rubricName" : "rubric1",
            "timeFrom" : ISODate("2018-08-20T07:20:00.000Z"),
            "activity" : false,
            "units" : [
                {
                    "unitId" : "40",
                    "status" : "new",
                    "bookingDay" : {
                        "dateValue" : ISODate("2018-08-15T21:00:00.000Z")
                    }
                }
            ]
        }
    ],
    "mediaId" : NumberLong(4)
}

You can do this with the aggregation framework now (see the linked duplicate), but also note that answer saying "The real answer is of course that unless you are really saving a lot of bandwidth by filtering out such detail then you should not even try, or at least beyond the first positional match." (i.e. just post-process in your application code instead) — Thilo, Aug 20 '18 at 08:43

score 0 · Answer 1 · answered Aug 20 '18 at 09:52

db.getCollection("doc").aggregate(

    // Pipeline
    [
        // Stage 1
        {
            $unwind: {
                path: "$rows",
                includeArrayIndex: "arrayIndex", // optional
                preserveNullAndEmptyArrays: false // optional
            }
        },

        // Stage 2
        {
            $project: {
                rows: {
                    units: {
                        $filter: {
                            input: "$rows.units",
                            as: "item",
                            cond: {
                                $eq: ["$$item.status", "new"]
                            }
                        }
                    }
                },
                _class: 1,
                mediaId: 1
            }
        },

    ]



);

MongoDB filtering by 3rd level embedded objects

1 Answers1