0

I have a company collection which looks like this.

{
    "_id" : ObjectId("5a897da6dffe7eb1a3028863"),

    "name" : "ABC",

    "website" : "http://www.abc.af",
    "alternativeEmail" : "abc@gmail.com",
    "address" : "Floor 1, Street 6",
    "type" : {
        "id" : ObjectId("5a897c81dffe7eb1a302885d"),
        "name" : "Company / Corporation"
    },
    "createdBy" : {
        "id" : ObjectId("5a897a21739589bbf635c9b4"),
        "firstName" : "Admin",
        "lastName" : "Admin"
    },
    "status" : 1,
    "socialLinks" : [],
    "__v" : 0
}

another collection companyType which has records like.

{
    "_id" : ObjectId("5a897c81dffe7eb1a302885d"),
    "updatedAt" : ISODate("2018-02-20T05:16:09.503Z"),
    "createdAt" : ISODate("2018-02-18T13:15:45.624Z"),
    "name_en" : "Company / Corporation",
    "name_da" : "Company / Corporation",
    "name_pa" : "Company / Corporation",
    "validPlans" : {
        "planId" : ObjectId("5a897b58dffe7eb1a3028858"),
        "planName" : "Company / Corporation",
        "billingType" : "Prepaid"
    },
    "__v" : 0
}.

Now, what I want is to filter company which companyType.validPlan.billingType === 'prePaid' where company.type.id = companyType._id.

I have written my query like this.

db.getCollection('companies').aggregate([
{
    $lookup: {
    "from": "companytypes",
    "localField": "type.id",
    "foreignField": "_id",
    "as": "companyType"    
    }
 }
]);

I am not sure if it is the solution because I can not filter by companyType.validPlan.billingType ==='prePaid'.

So how can I achieve it?

Ali Razmjo
  • 73
  • 10
  • Why not? Surely you can filter by `billingType`. – Alex Blex Feb 20 '18 at 13:11
  • Add `{$unwind:"$companyType"}, {$match:{"companyType.validPlan.billingType":"prePaid"}}` after `$lookup` – s7vr Feb 20 '18 at 13:22
  • db.getCollection('companies').aggregate([ { $lookup: { "from": "companytypes", "localField": "type.id", "foreignField": "_id", "as": "companyType" }, }, { $unwind:"$companyType" }, { $match: { "$companyType.validPlan.billingType":"Prepaid" } } ]); does not work. – Ali Razmjo Feb 20 '18 at 13:30
  • You have $ in match stage. should just be `{ $match: { "companyType.validPlan.billingType":"Prepaid" } }` – s7vr Feb 20 '18 at 13:41
  • Thanks, it works by now – Ali Razmjo Feb 20 '18 at 13:45

0 Answers0