MongoDB multiple fields lookup

Question

I'm trying to perform a $lookup using two fields on MongoDB 3.6. I've already read the docs and similar questions here, but I was unable to find what's wrong.

Collection acls:

[ { _id: 1, FolderId: 4, Sid: 'S-123-456' }
  { _id: 2, FolderId: 5, Sid: 'S-234-567' }
  { _id: 3, FolderId: 6, Sid: 'S-345-678' } ]

Collection groups:

[ { _id: 1, ProcessId: 10, Sid: 'S-123-456', Users: [ 'user1', 'user2'] }
  { _id: 2, ProcessId: 10, Sid: 'S-234-567', Users: [ 'user1'] }
  { _id: 3, ProcessId: 20, Sid: 'S-123-456', Users: [ 'user2'] } ]

Query:

db.acls.aggregate({
    $lookup: 
    { 
        from: 'groups',
        let: { 'ProcessId': 10, 'GroupSid': '$Sid' },
        pipeline: [{
                   $match: {
                      $expr: {
                         $and: [
                            {
                               $eq: [ '$ProcessId', '$$ProcessId' ]
                            },
                            {
                               $eq: [ '$Sid', '$$GroupSid' ]
                            }
                         ]
                      }
                   }
                }],
        as: 'grouplist'
    }
})

I was expecting to return something like:

{ _id: 1, FolderId: 4, Sid: 'S-123-456', 
  grouplist: [ { _id: 1, ProcessId: 10, Sid: 'S-123-456', Users: [ 'user1', 'user2'] }] }

but instead I'm getting 'Script executed successfully, but there are no results to show', on Robo 3T.

Ashh · Answer 1 · 2018-10-04T14:24:07.307

1

$let variable operator must start with the lower case letter.

db.acls.aggregate([
  { "$lookup": { 
    "from": 'groups',
    "let": { "groupSid": "$Sid" },
    "pipeline": [
      { "$match": {
        "$expr": { "$eq": [ "$Sid", "$$groupSid" ] },
        "ProcessId": 10
      }}
    ],
    "as": "grouplist"
  }}
])

edited Oct 04 '18 at 14:24

answered Oct 04 '18 at 13:39

Ashh

44,693
14
105
132

Same message: `'Script executed successfully, but there are no results to show'` – Anderson Pimentel Oct 04 '18 at 13:43
Updated the answer please have a look – Ashh Oct 04 '18 at 13:48
Works fine, but I'm accepting the other answer because he pointed out the culprit: `let` variables should start with lowercase characters. Thank you very much for your time. – Anderson Pimentel Oct 04 '18 at 14:07
1

Ok no issue. You can check the updated answer with links – Ashh Oct 04 '18 at 14:24

score 1 · Accepted Answer · answered Oct 04 '18 at 13:51

Try This it's working fine. Your let keyword must be start with lowercase

    db.acls.aggregate([
   {
      $lookup:
         {
           from: "groups",
           let: { processid: 10, sid: "$Sid" },
           pipeline: [
              { $match:
                 { $expr:
                    { $and:
                       [
                         { $eq: [ "$ProcessId",  "$$processid" ] },
                         { $gte: [ "$Sid", "$$sid" ] }
                       ]
                    }
                 }
              }
           ],
           as: "grouplist"
         }
    }
])

It worked! I couldn't find anywhere in the docs that `let` variables should start with lowercase characters, but that was the main problem. Thank you very much! — Anderson Pimentel, Oct 04 '18 at 14:09

score 0 · Answer 3 · answered Oct 05 '18 at 07:35

db.getCollection("acls").aggregate(

    // Pipeline
    [
        // Stage 1
        {
            $lookup: // Equality Match
            {
                from: "groups",
                localField: "Sid",
                foreignField: "Sid",
                as: "grouplist"
            }


        },

        // Stage 2
        {
            $project: {
                grouplist: {
                    $filter: {
                        input: "$grouplist",
                        as: "group",
                        cond: {
                            $eq: ["$$group.ProcessId", 10]
                        }
                    }
                },

                FolderId: 1,
                Sid: 1
            }
        },

    ]



);

MongoDB multiple fields lookup

3 Answers3