当外来字段是ID数组时的MongoDB查找

debugcn 发表于 Dev

普拉卡什（Prakash Ranjan）

我有两个系列，水果和推销员。我希望我的查询返回所有以逗号分隔的业务员的水果。

推销员文档具有水果ID数组

水果文档具有ID，名称，........推销员表具有ID，名称，水果[apple_id，mango_id .......]，...

db.getCollection('fruit').aggregate([{ "$unwind": "$fruits" }, { "$lookup": {
       "from": "salesman",
       "localField": "fruits",
       "foreignField": "_id",
       "as": "fruitObjects"
    }},
    { "$unwind": "$fruitObjects" } ])

甚至这个查询也没有给出$ fruitObjects ..的结果？

水果文件

{
    "_id" : ObjectId("5b101caddcab7850a4ba32eb"),
    "name" : "Mango"
}

{
    "_id" : ObjectId("5b101caddcab7850a4ba32ec"),
    "name" : "Pears"
}

{
   "_id" : ObjectId("5b101caddcab7850a4ba32de"),
    "name" : "apple"
}

{
   "_id" : ObjectId("5b101caddcab7850a4ba32fe"),
    "name" : "guava"
}

推销员文件

{
    "_id" : ObjectId("5b101caddcab7850a4ba3257"),
    "name" : "xyz",
    "fruits":["5b101caddcab7850a4ba32ec","5b101caddcab7850a4ba32de","5b101caddcab7850a4ba32fe"]
}

{
    "_id" : ObjectId("5b101caddcab7850a4ba3258"),
    "name" : "abc",
    "fruits":["5b101caddcab7850a4ba32eb","5b101caddcab7850a4ba32de"]
}

{
   "_id" : ObjectId("5b101caddcab7850a4ba3259"),
    "name" : "def",
    "fruits":["5b101caddcab7850a4ba32ec"]
}

{
   "_id" : ObjectId("5b101caddcab7850a4ba3260"),
    "name" : "zxc",
    "fruits":["5b101caddcab7850a4ba32ec","5b101caddcab7850a4ba32de","5b101caddcab7850a4ba32eb"]
}
``````````````````````````

谁-假面真灵魂

是的@barrypicker是正确的-字段类型不匹配，导致上为空salesman，请尝试存储相同类型的字段。同时，您实际上可以将一种转换为其他类型-每次查询时，也不需要这样做$unwind，请尝试以下查询：

查询1：

db.fruit.aggregate([
    {
        $lookup:
        {
            from: "salesman",
            let: { fruitName: { $toString: '$_id' } },
            pipeline: [
                {
                    $match:
                    {
                        $expr:
                            { $in: ["$$fruitName", "$fruits"] }
                    }
                }, { $project: { name: 1, _id: 0 } }
            ],
            as: "salesman"
        }
    }])

查询1的结果：

/* 1 */
{
    "_id" : ObjectId("5b101caddcab7850a4ba32eb"),
    "name" : "Mango",
    "salesman" : [ 
        {
            "name" : "abc"
        }, 
        {
            "name" : "zxc"
        }
    ]
}

/* 2 */
{
    "_id" : ObjectId("5b101caddcab7850a4ba32ec"),
    "name" : "Pears",
    "salesman" : [ 
        {
            "name" : "def"
        }, 
        {
            "name" : "zxc"
        }
    ]
}

/* 3 */
{
    "_id" : ObjectId("5b101caddcab7850a4ba32de"),
    "name" : "apple",
    "salesman" : [ 
        {
            "name" : "abc"
        }, 
        {
            "name" : "zxc"
        }
    ]
}

/* 4 */
{
    "_id" : ObjectId("5b101caddcab7850a4ba32fe"),
    "name" : "guava",
    "salesman" : []
}

或者，如果您希望这些名称位于数组中：

查询2：

db.fruit.aggregate([
    {
        $lookup:
        {
            from: "salesman",
            let: { fruitName: { $toString: '$_id' } },
            pipeline: [
                {
                    $match:
                    {
                        $expr:
                            { $in: ["$$fruitName", "$fruits"] }
                    }
                }, { $project: { name: 1, _id: 0 } }
            ],
            as: "salesmanList"
        }
    }, {
        $project: {
            name: 1, salesman: {
                $map:
                {
                    input: "$salesmanList",
                    as: "each",
                    in: '$$each.name'
                }
            }
        }
    }])

查询2的结果：

/* 1 */
{
    "_id" : ObjectId("5b101caddcab7850a4ba32eb"),
    "name" : "Mango",
    "salesman" : [ 
        "abc", 
        "zxc"
    ]
}

/* 2 */
{
    "_id" : ObjectId("5b101caddcab7850a4ba32ec"),
    "name" : "Pears",
    "salesman" : [ 
        "def", 
        "zxc"
    ]
}

/* 3 */
{
    "_id" : ObjectId("5b101caddcab7850a4ba32de"),
    "name" : "apple",
    "salesman" : [ 
        "abc", 
        "zxc"
    ]
}

/* 4 */
{
    "_id" : ObjectId("5b101caddcab7850a4ba32fe"),
    "name" : "guava",
    "salesman" : []
}

水果收集：

/* 1 */
{
    "_id" : ObjectId("5b101caddcab7850a4ba32eb"),
    "name" : "Mango"
}

/* 2 */
{
    "_id" : ObjectId("5b101caddcab7850a4ba32ec"),
    "name" : "Pears"
}

/* 3 */
{
    "_id" : ObjectId("5b101caddcab7850a4ba32de"),
    "name" : "apple"
}

/* 4 */
{
    "_id" : ObjectId("5b101caddcab7850a4ba32fe"),
    "name" : "guava"
}

推销员集合：

/* 1 */
{
    "_id" : ObjectId("5b101caddcab7850a4ba3258"),
    "name" : "abc",
    "fruits" : [ 
        "5b101caddcab7850a4ba32eb", 
        "5b101caddcab7850a4ba32de"
    ]
}

/* 2 */
{
    "_id" : ObjectId("5b101caddcab7850a4ba3259"),
    "name" : "def",
    "fruits" : [ 
        "5b101caddcab7850a4ba32ec"
    ]
}

/* 3 */
{
    "_id" : ObjectId("5b101caddcab7850a4ba3260"),
    "name" : "zxc",
    "fruits" : [ 
        "5b101caddcab7850a4ba32ec", 
        "5b101caddcab7850a4ba32de", 
        "5b101caddcab7850a4ba32eb"
    ]
}

如果你想从整个对象salesman，那么你可以删除{ $project: { name: 1, _id: 0 } }在$lookup。

参考： $ lookup，$ map

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。