[SERVER-34993] $lookup defined array variables Created: 15/May/18  Updated: 27/Oct/23  Resolved: 16/May/18

Status: Closed
Project: Core Server
Component/s: Aggregation Framework
Affects Version/s: 3.6.0, 3.6.4
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Filip Assignee: Unassigned
Resolution: Works as Designed Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Operating System: ALL
Steps To Reproduce:

db.test.insert({ foo: [1, 2] })
db.test.insert({ bar: 1 })

db.test.aggregate([
    {
        $match: {
            foo: 1
        }
    },
    {
        $lookup: {
            from: 'test',
            let: {
                arr: '$foo'
            },
            pipeline: [
                {
                    $match: {
                        bar: {
                            $in: '$$arr'
                        }
                    }
                }
            ],
            as: 'result'
        }
    }
])

and it will throw:

{ 
    "operationTime" : Timestamp(1526389658, 1), 
    "ok" : 0.0, 
    "errmsg" : "$in needs an array", 
    "code" : NumberInt(2), 
    "codeName" : "BadValue", 
    "$clusterTime" : {
        "clusterTime" : Timestamp(1526389658, 1), 
        "signature" : {
            "hash" : BinData(0, "AAAAAAAAAAAAAAAAAAAAAAAAAAA="), 
            "keyId" : NumberLong(0)
        }
    }
}

but projection shows:

db.test.aggregate([
    {
        $match: {
            foo: 1
        }
    },
    {
        $lookup: {
            from: 'test',
            let: {
                arr: '$foo'
            },
            pipeline: [
                {
                    $match: {
                        bar:1
                    }
                },
                {
                    $project: {
                        arr: '$$arr'
                    }
                }
            ],
            as: 'result'
        }
    }
])

that $$arr is in fact an array:

{ 
    "_id" : ObjectId("5afad86a91c788853024d506"), 
    "foo" : [
        1.0, 
        2.0
    ], 
    "result" : [
        {
            "_id" : ObjectId("5afadc2c91c788853024d507"), 
            "arr" : [
                1.0, 
                2.0
            ]
        }
    ]
}

Participants:

 Description   

It seems that array expression operators they do not see the defined variables as arrays.



 Comments   
Comment by Charlie Swanson [ 16/May/18 ]

Hi aPoCoMiLogin,

It looks like the '$$arr' in your $lookup pipeline is being treated as a string literal. If you want to reference a variable you have to use the $expr predicate.

In order to get the results you want, I think this might work:

db.test.aggregate([
    {
        $match: {
            foo: 1
        }
    },
    {
        $lookup: {
            from: 'test',
            let: {
                arr: '$foo'
            },
            pipeline: [
                {
                    $match: {$expr: {$in: ["$bar", "$$arr"]}}}
                }
            ],
            as: 'result'
        }
    }
])

The syntax is pretty confusing here, but sometimes "$$thing" is a valid string to use in a match, so we need to escape it somehow.

Generated at Thu Feb 08 04:38:29 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.