0%
June 15, 2023

Two Kinds of $lookup, and use Javascript in Advanced lookup

mongo

Contents

First $lookup

1db.ns_material_categories.aggregate(
2    [
3        {
4            $lookup: {
5                from: "ns_material_category_groups",
6                localField: "group_code",
7                foreignField: "code",
8                as: "properties"
9            }
10        },

The $lookup here is the most basic form, which do a left join by gluing group_code and code.

Second $lookup

The next one is a generalization of the first $lookup .

11        {
12            $unwind: "$properties"
13        },
14        {
15            $lookup: {
16                let: { code: "$code" },
17                from: "ns_materials",
18                pipeline: [
19                    {
20                        $match: {
21                            $expr: {
22                                $and: [
23                                    {$eq: ["$category_code", "$$code"]},
24                                    {$eq: ["$is_dummy", "Y"]}
25                                ]
26                            }
27                        }
28                    },
29                    {
30                        $project: {
31                            _id: 0,
32                            manu_code: 1,
33                            model_code: 1,
34                            name: 1
35                        }
36                    }
37                ],
38                as: "suppliers"
39            }
40        }
41    ]
42)

This is somewhat more complicated, but the pipeline makes it much more flexible than the basic form.

The from-let-pipeline-as lookup

For look up we have either

  • from-localField-foreignField-as
  • from-let-pipeline-as There is no pipeline when either localField or foreignField exists.

We try to break down to explain the more advanced $lookup, we start form line 16 of the code blocks above.

  • In let we have ${code: "$code"}, the key code is the variable name for temp storage, the "$code" is the field path of an object from the upstream collection in the pipeline.

  • We store all desired variable in the let stage. In this example, we will unwrap and utilize the value by writing "$$code".

  • We use $expr to instantiate any comparison.

  • The basic $lookup can just do

    select a.* from A
left join B b on b.a_id = a.id

  • whereas the advanced $lookup can do

    select b.sth1, b.sth2, a.* from A a
left join B b on b.id = b_id and ... and ... and ...

    due to the field pipeline.

Using Javascript in $lookup with Pipeline!

pipeline: [
    {
        $lookup: {
            from: "ns_generic_form_templates",
            let: { code: "$code" },
            pipeline: [
                { $project: { subtypes: 1 } },
                {
                    $addFields: {
                        formAnalysis: {
                            $function: {
                                body: function (code, subtypes, formId) {
                                    if (!subtypes) {
                                        return null
                                    } else {
                                        const hasForm = subtypes.indexOf(code) > -1;

                                        return { category_code: code, hasForm, formOid: formId }
                                    }
                                },
                                args: ["$$code", "$subtypes", "$_id"],
                                lang: 'js'
                            }
                        }
                    }
                },
                { $project: { subtypes: 0 } }
                {
                $match: {
                    "formAnalysis.hasForm": true
                }
                },
                { $limit: 1 },
                { $replaceRoot: { newRoot: "$formAnalysis" } },
                { $set: { formOid: { $toString: "$formOid" } } },
            ],
            as: "genericForm"
        }
    },
    {
        $unwind: "$genericForm"
    }
]