How do I join collections based on a substring in MongoDB-CodePudding

Just trying to wrap my head around how to reference a collection based on a substring match.

Say I have one collection with chat information (including cell_number: 10 digits). I have another collection in which every document includes a field for area code (3 digits) and some other information about that area code, like so:

Area codes collection excerpt:

{
    "_id" : ObjectId("62cf3d56580efcedf7c3b845"),
    "code" : "206",
    "region" : "WA",
    "abbr" : "WA"
}
{
    "_id" : ObjectId("62cf3d56580efcedf7c3b846"),
    "code" : "220",
    "region" : "OH",
    "abbr" : "OH"
}
{
    "_id" : ObjectId("62cf3d56580efcedf7c3b847"),
    "code" : "226",
    "region" : "Ontario",
    "abbr" : "ON"
}

What I want to do is be able to grab out just the document from the area codes collection which has a "code" field value matching the first 3 characters of a cell number. A simple example is below. Note that in this example I have hardcoded the cell number for simplicity, but in reality it would be coming from the chat_conversations "parent" collection:

db.chat_conversations.aggregate([
    {$match: {wait_start: {$gte: new Date("2020-07-01"), $lt: new Date("2022-07-12")}}},
    {$lookup: {
        from: "area_codes",
        let: {
            areaCode: "$code",
            cellNumber: "2065551234"
        },
        pipeline: [
            {$match: {
                $expr: {
                    $eq: ["$$areaCode", {$substr: ["$$cellNumber", 0, 3]}]
                }
            }}
        ],
        as: "area_code"
    }},
]).pretty()

Unfortunately nothing I try here seems to work and I just get back an empty array from the area codes collection.

What am I missing here?

CodePudding user response：

you should do a let on chat_converstion

or you should use let with vars and in expressions

this query should work for you

db.chat_conversations.aggregate([
    {$match: {wait_start: {$gte: new Date("2020-07-01"), $lt: new Date("2022-07-12")}}},
    {
        $lookup: {
            from: "area_codes",
            let:{
                "cell_Number":{$substr:["$cellNumber",0,3]}
            },
            pipeline:[
            {
                $match:{
                    $expr:{
                        $eq: [ "$code",  "$$cell_Number" ]
                    }
                }
            }],
            as: "data"
        }
    },
])