MongoDB 按字段查找对象数组(连接条件和不相关的子查询) [英] MongoDB lookup array of objects by field (join conditions and uncorrelated sub-queries)

问题描述

我不明白使用 加入条件和不相关的子查询.

进程集合:

{
    _id: 'p1',
    steps: [
        {
            _id: 'ps1',
            step: 's1',
            time: 10
        },
        {
            _id: 'ps2',
            step: 's2',
            time: 15
        }
    ]
}

steps 集合(对于具有 _id: s1 的文档):

steps collection (for the document with _id: s1):

{
    _id: 's1',
    name: 'step 1'
}

工作聚合(标准一，没有连接条件和不相关的子查询):

Working aggregation (standard one, without join conditions and uncorrelated sub-queries):

processes.aggregate([
    {
        // match stage or whatever prior stage
    },
    {
        $lookup: {
             from: 'steps',
             localField: 'steps.step',
             foreignField: '_id',
             as: 'steps'
        }
    }
])

输出:

{
    _id: 'p1',
    steps: [
        {
            _id: 's1',
            name: 'step 1'
        },
        {
            _id: 's2',
            name: 'step 2'
        }
    ]
}

聚合不起作用:

processes.aggregate([
    {
        // match stage or whatever prior stage
    },
    {
        $lookup: {
            from: 'steps',
            let: { stepId: '$steps.step' }, // I think the problem is here
            pipeline: [
                {
                    $match: {
                        $expr: { $eq: ['$_id', '$$stepId'] },
                    },
                },
                {
                    // Additional stages here
                }
            ],
            as: 'steps',
        },
    }
])

输出:

{
    _id: 'p1',
    steps: []
}

推荐答案

steps.step 在这种情况下计算为字符串数组 ["s1", "s2"].常规的 $lookup 支持这种比较，并在后台执行 $in.

steps.step evaluates to an array of strings in this case ["s1", "s2"]. The regular $lookup supports such comparison and does $in behind the scenes.

在您的第二个示例中，您使用的是 $expr 因此您需要使用表达式语言，因此您必须使用 $in 运算符:

In your second example you're using $expr so you need to use expression language hence you have to use $in operator:

$expr: { $in: ['$_id', '$$stepId'] }

Mongo 游乐场

这篇关于MongoDB 按字段查找对象数组(连接条件和不相关的子查询)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！