How to use $filter in nested child array with mongodb?

Question

My mongodb data is like this,i want to filter the memoryLine.

{
"_id" : ObjectId("5e36950f65fae21293937594"),
"userId" : "5e33ee0b4a3895a6d246f3ee",
"notes" : [ 
    {
        "noteId" : ObjectId("5e36953665fae212939375a0"),
        "time" : ISODate("2020-02-02T17:24:06.460Z"),
        "memoryLine" : [ 
            {
                "_id" : ObjectId("5e36953665fae212939375ab"),
                "memoryTime" : ISODate("2020-02-03T17:54:06.460Z")
            }, 
            {
                "_id" : ObjectId("5e36953665fae212939375aa"),
                "memoryTime" : ISODate("2020-02-03T05:24:06.460Z")
            }
        ]
    }
]}

i want to get the item which memoryTime is great than now as expected like this,

"userId" : "5e33ee0b4a3895a6d246f3ee",
"notes" : [ 
    {
    "noteId" : ObjectId("5e36953665fae212939375a0"),
    "time" : ISODate("2020-02-02T17:24:06.460Z"),
    "memoryLine" : [ 
        {
            "_id" : ObjectId("5e36953665fae212939375ab"),
            "memoryTime" : ISODate("2020-02-03T17:54:06.460Z")
        }, 
        {
            "_id" : ObjectId("5e36953665fae212939375aa"),
            "memoryTime" : ISODate("2020-02-03T05:24:06.460Z")
        }
    ]
}]

so is use code as below.i use a $filter in memoryLine to filter to get the right item.

aggregate([{
    $match: {
        "$and": [
            { userId:  "5e33ee0b4a3895a6d246f3ee"},
        ]
    }
}, {
    $project: {
        userId: 1,
        notes: {
            noteId: 1,
            time: 1,
            memoryLine: {
                $filter: {
                    input: "$memoryLine",
                    as: "mLine",
                    cond: { $gt: ["$$mLine.memoryTime", new Date(new Date().getTime() + 8 * 1000 * 3600)] }
                }
            }
        }
    }
}]).then(doc => {
    res.json({
        code: 200,
        message: 'success',
        result: doc
    })
});

but i got this,memoryLine is null,why?I try to change $gt to $lt, but also got null.

"userId" : "5e33ee0b4a3895a6d246f3ee",
"notes" : [ 
    {
    "noteId" : ObjectId("5e36953665fae212939375a0"),
    "time" : ISODate("2020-02-02T17:24:06.460Z"),
    "memoryLine" : null  <<<-------------  here is not right
}]

mickl · Accepted Answer · 2020-02-02 15:13:39Z

1

You can use $addFields to replace existing field, $map for outer collection and $filter for inner:

db.collection.aggregate([
    {
        $addFields: {
            notes: {
                $map: {
                    input: "$notes",
                    in: {
                        $mergeObjects: [
                            "$$this",
                            { 
                                memoryLine: {
                                    $filter: {
                                        input: "$$this.memoryLine",
                                        as: "ml",
                                        cond: {
                                            $gt: [ "$$ml.memoryTime", new Date() ]
                                        }
                                    }
                                }
                            }
                        ]
                    }
                }
            }
        }
    }
])

$mergeObjects is used to avoid repeating fields from source memoryLine object.

Mongo Playground

answered Feb 2, 2020 at 15:13

mickl

50.1k9 gold badges71 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JackieWillen Over a year ago

thank you very much.Can you tell me why my code with filter is wrong.I study from here docs.mongodb.com/manual/reference/operator/aggregation/filter

mickl Over a year ago

@JackieWillen single $filter is totally fine when you have single array. In your case your array is nested inside of another array so you need to deal with the outer one first - that's why you need to run $map

Collectives™ on Stack Overflow

How to use $filter in nested child array with mongodb?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related