MongoDB中查找时洋场是一个数组 [英] MongoDB lookup when foreign field is an array

问题描述

我在互联网和 StackOverflow 上搜索过，但我找不到答案，甚至找不到问题.

I've searched the internet and StackOverflow, but I cannot find the answer or even the question.

我有两个集合，reports 和 users.我希望我的查询返回所有报告并指出指定用户是否将该报告作为其数组中的收藏夹.

I have two collections, reports and users. I want my query to return all reports and indicate if the specified user has that report as a favorite in their array.

报告集

{ _id: 1, name:"Report One"}
{ _id: 2, name:"Report Two"}
{ _id: 3, name:"Report Three"}

用户集合

{_id: 1, name:"Mike", favorites: [1,3]}
{_id: 2, name:"Tim", favorites: [2,3]}

有users.name的希望的结果= 迈克"

Desired Result for users.name="Mike"

{ _id: 1, name:"Report One", favorite: true}
{ _id: 2, name:"Report Two", favorite: false}
{ _id: 3, name:"Report Three", favorite: true}

我能找到的所有答案都在本地 (reports) 字段上使用 $unwind，但在这种情况下，本地字段不是数组.外部字段是数组.

All of the answers I can find use $unwind on the local (reports) field, but in this case the local field isn't an array. The foreign field is the array.

如何解开异域?有没有更好的方法来做到这一点?

How can I unwind the foreign field? Is there a better way to do this?

我在网上看到有人建议制作另一个收藏夹集合，其中包含:

I saw online that someone suggested making another collection favorites that would contain:

{ _id: 1, userId: 1, reportId: 1 }
{ _id: 2, userId: 1, reportId: 3 }
{ _id: 3, userId: 2, reportId: 2 }
{ _id: 4, userId: 2, reportId: 3 }

这个方法看起来应该是不必要的.连接到外部数组中的 ID 应该很简单，对吗?

This method seems like it should be unnessesary. It should be simple to join onto an ID in a foreign array, right?

推荐答案

可以使用$lookup with custom pipeline 这会给你 0 或 1 结果，然后使用 $size 将数组转换为单个布尔值:

You can use $lookup with custom pipeline which will give you 0 or 1 result and then use $size to convert an array to single boolean value:

db.reports.aggregate([
    {
        $lookup: {
            from: "users",
            let: { report_id: "$_id" },
            pipeline: [
                {
                    $match: {
                        $expr: {
                            $and: [
                                { $eq: [ "$name", "Mike" ] },
                                { $in: [ "$$report_id", "$favorites" ] }
                            ]
                        }
                    }
                }
            ],
            as: "users"
        }
    },
    {
        $project: {
            _id: 1,
            name: 1,
            favorite: { $eq: [ { $size: "$users" }, 1 ] }
        }
    }
])

或者，如果您需要使用低于 3.6 的 MongoDB 版本，您可以使用常规的 $lookup，然后使用 $filter 只获取那些 name 是 Mike 的用户:

Alternatively if you need to use MongoDB version lower than 3.6 you can use regular $lookup and then use $filter to get only those users where name is Mike:

db.reports.aggregate([
    {
        $lookup: {
            from: "users",
            localField: "_id",
            foreignField: "favorites",
            as: "users"
        }
    },
    {
        $project: {
            _id: 1,
            name: 1,
            favorite: { $eq: [ { $size: { $filter: { input: "$users", as: "u", cond: { $eq: [ "$$u.name", "Mike" ] } } } }, 1 ] }
        }
    }
])

这篇关于MongoDB中查找时洋场是一个数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！