查找管道：`$ match`本地字段`$ in`数组，当from值是数组且要找到本地值时 [英] Lookup pipeline: `$match` local field `$in` array when from value is the array and local value is to be found

问题描述

我有2个收藏集。我想对集合A进行汇总，使其与ID匹配。从那里，我要从集合B查找，其中集合A（本地字段）的匹配ID在集合B（外部字段）的数组中。

I have 2 collections. I want to do an aggregate on Collection A where it matches an ID. From there, I want to lookup from Collection B where the matched ID from Collection A (local field) is in the array of Collection B (foreign field).

所以基本上：

集合A：

{
    _id: ObjectId('<id>')
},
{
    _id: ObjectId('<id>')
},
{
    _id: ObjectId('<id>')
}

集合B：

{
    _id: ObjectId('<id>'),
    related: ['<id>', '<id>', '<id>', '<id>']
},
{
    _id: ObjectId('<id>'),
    related: ['<id>', '<id>', '<id>', '<id>']
},
{
    _id: ObjectId('<id>'),
    related: ['<id>', '<id>', '<id>', '<id>']
}

查询：

db.collection_a.aggregate({
    [$match: {_id: ObjectId('<id>')}],
    // other $lookups...
    [
        $lookup: {
            as: 'collection_b',
            from: 'collection_b',
            let: {id: '$_id'},
            pipeline: [
                {
                    $match: {
                        $expr: {$in: ['$related', '$$id']}
                    }
                }
                // sorts, projections, etc...
            ]
        }
    ]
    // sorts, projections, etc...
});

所需结果：

[
    {
        _id: ObjectId('<id>'),
        collection_b: [
            {
                _id: ObjectId('<id>'),
                related: ['<id>', '<id>', '<id>', '<id>']
            },
            {
                _id: ObjectId('<id>'),
                related: ['<id>', '<id>', '<id>', '<id>']
            }
        ]
    },
]

结果：

$in requires an array as a second argument, found: objectId

---

现在，我知道可以对此进行切换并在集合B上查找集合A ，但是，在这种情况下，这不是一个选择（即使在集合B的任何文档中不存在集合A，也应查询集合A）。最好将其保留为一个查询。

---

Now, I know that switching this up and looking up Collection A on Collection B is possible, however, this is not an option in this instance (Collection A should still be queried, even if it is not present in any document in Collection B). Preferable to keep this to one query.

非常感谢您的帮助。

推荐答案

您可以尝试以下操作吗：

Can you please try this :

db.collection_a.aggregate(
[{ $match: { _id: ObjectId(' ') } },
// other $lookups...
{
    $lookup: {
        as: 'collection_b',
        from: 'collection_b',
        let: { id: { $toString: '$_id' } },
        pipeline: [
            {
                $addFields: {
                    "related": {
                        "$cond": {
                            "if": {
                                "$ne": [{ "$type": "$related" }, "array"]
                            },
                            "then": [],
                            "else": "$related"
                        }
                    }
                }
            },
            {
                $match: {
                    $expr: { $in: ['$$id', '$related'] }
                }
            }
            // sorts, projections, etc...
        ]
    }
}
           // sorts, projections, etc...
]);

它说 $ in 需要一个数组作为第二个参数，而您要搜索的值就是第一个参数。

As it says $in takes an array as second parameter and a value you're searching for to be first parameter.

这篇关于查找管道：`$ match`本地字段`$ in`数组，当from值是数组且要找到本地值时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！