Mongodb lookup with match in aggregate returns an empty array

Question

This is my user document

{
   "_id":"02a33b9a-284c-4869-885e-d46981fdd679",
   "context":{
      "email":"[email protected]",
      "firstName":"John",
      "lastName":"Smith",
      "company":[
         "e2467c93-114b-4613-a842-f311a8c537b3"
      ],
   },
}

and a company document

{
   "_id":"e2467c93-114b-4613-a842-f311a8c537b3",
   "context":{
      "name":"Coca Cola",
      "image":"someimage",
   },
};

This is my query for users

let users = await Persons.aggregate(
            [{$project:
            {
                name: {$concat: ['$context.firstName', ' ', '$context.lastName']},
                companyId: {$arrayElemAt: ['$context.company', 0]}}
            },
            {$match: {name: searchRegExp}},
            {$lookup: {from: 'companies', let: {company_id: {$arrayElemAt: ['$context.company', 0]}}, pipeline:
            [
                {
                    $match: {
                        $expr: {
                            $eq: ['$_id', '$$company_id']
                        }
                    }
                },
                {
                    $project: {name: '$context.name'}
                }
            ],
            as: 'company'}}
            ]).toArray()

When I run this query I get company field as an empty array, what am I doing wrong here?

mickl · Accepted Answer · 2020-03-12 13:54:47Z

2

Your first pipeline stage $project only outputs _id, name and companyId so then when you're trying to refer to $context.company in your $lookup there will be an empty value. You can use $addFields instead:

{
    $addFields: {
        name: {
            $concat: [
                "$context.firstName",
                " ",
                "$context.lastName"
            ]
        },
        companyId: {
            $arrayElemAt: [
                "$context.company",
                0
            ]
        }
    }
}

Mongo Playground

answered Mar 12, 2020 at 13:54

mickl

50.1k9 gold badges71 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Wernfried Domscheit · Accepted Answer · 2020-03-12 14:30:30Z

1

When you add field companyId: {$arrayElemAt: ['$context.company', 0]}}, then you can use the simple version of $lookup. There is no need to set it twice, once as companyId: ... and in let: {company_id: ...}

db.user.aggregate([
   {
      $addFields: {
         name: { $concat: ["$context.firstName", " ", "$context.lastName"] },
         companyId: { $arrayElemAt: ["$context.company", 0] } 
      }
   },
   {
      $lookup: {
         from: "company",
         localField: "companyId",
         foreignField: "_id",
         as: "company"
      }
   }
])

answered Mar 12, 2020 at 14:30

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

Collectives™ on Stack Overflow

Mongodb lookup with match in aggregate returns an empty array

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related