Mongodb aggregate $lookup $project and $match do not work

Question

I need your help. I am practicing very simple join method with filter on my two collection. It works without filter. My mongodb query is mention below.

Two Collection

First collection

{
    "_id": ObjectId("5ea18cd00715f152a6fcee24"),
    "company_name": "COMPANY 1",
    "company_detail_id": [
            {
                "_id": ObjectId("5e5db61dbb9d1523f7d78e65") //NOTE: company_detail_id == programs._id
            }
    ],
    "is_deleted": false,
}

Second Collection

{
     "_id": ObjectId("5e5db61dbb9d1523f7d78e65"),
     "name": "PROGRAM 1",
     "is_deleted": false
}

Query

var list = await dataModel.aggregate([
            {
                $lookup: {
                    from: "company_details",
                    localField: "company_detail_id._id",
                    foreignField: "_id",
                    as: "programs"
                }
            },
            {
                "$project": {
                            "_id": true,
                            "company_name": true,
                            "company_detail_id": true,
                            "is_deleted": true,
                            "programs._id": true,
                            "programs.name": true,
                            "programs.is_deleted": true
                        }
            },
            {
                "$match": {
                            "_id": "5ea18cd00715f152a6fcee24",
                            "is_deleted":false
                            }
            }
        ]).skip(0).limit(10);

Issue

This query WORKS when

    "$match" : {} 
    or
    "$match" : { "company_name": "xyz" }

This query NOT WORKING when

    "$match" : { "_id" : "5ea18cd00715f152a6fcee24" } 
    or
    "$match" : { "_id": "5ea18cd00715f152a6fcee24", "is_deleted":false }

Expected Result

{
    "_id": "5ea18cd00715f152a6fcee24",
    "company_name": "COMPANY 1",
    "company_detail_id": [
            {
                "_id": "5e5db61dbb9d1523f7d78e65" //NOTE: company_detail_id == programs._id
            }
    ],
    "is_deleted": false,
    "programs": [
            {
                 "_id": "5e5db61dbb9d1523f7d78e65",
                 "name": "PROGRAM 1",
                 "is_deleted": false
            }
    ]
}

score 1 · Accepted Answer · answered Apr 24 '20 at 04:45

1

I found SOLUTION

I include

const {ObjectId} = require('mongodb');

Then in $match I use

"$match" : { "_id" : ObjectId("5ea18cd00715f152a6fcee24") }

answered Apr 24 '20 at 04:45

Naisarg Parmar

759
8
25

ambianBeing · Answer 2 · 2020-04-24T04:52:42.690

The $match stage with { "_id" : "5ea18cd00715f152a6fcee24" } as its part is not working because its trying to match an ObjectId with a string.

Since Mongoose is used here, convert that string to a valid mongodb ObjectId using mongoose.Types.ObjectId('5ea18cd00715f152a6fcee24').

Query:

var mongoose = require("mongoose");
var list = await dataModel
  .aggregate([
    {
      $lookup: {
        from: "company_details",
        localField: "company_detail_id._id",
        foreignField: "_id",
        as: "programs",
      },
    },
    {
      $project: {
        _id: true,
        company_name: true,
        company_detail_id: true,
        is_deleted: true,
        "programs._id": true,
        "programs.name": true,
        "programs.is_deleted": true,
      },
    },
    {
      $match: {
        _id: mongoose.Types.ObjectId("5ea18cd00715f152a6fcee24"),
        is_deleted: false,
      },
    },
  ])
  .skip(0)
  .limit(10);

Playground Link

Yeah, you posted the solution just as I was editing the query :). Also a suggestion: you don't have use a separate package `mongodb` to convert a string to `objectId`. Mongoose is sufficient. — ambianBeing, Apr 24 '20 at 04:50

Mongodb aggregate $lookup $project and $match do not work

2 Answers2