How to join in mongo query with Array of IDs?

Question

Below is my TEST table object which contains category id array

{
    "_id" : ObjectId("5ede075651957a5d540c7828"),
    "Tags" : [ 
        "demo", 
        "skype"
    ],
    "CategoryID" : [ 
        "5edb65a834c5cc735a76b8e4", 
        "5edb65ae34c5cc735a76b8e5"
    ],
    "Title" : "demo 1",
    "Description" : "<p>sdfsdf</p>",
    "MetaTitle" : "asd",
    "MetaDescription" : "asdas",
    "MetaKeywords" : "asd",
    "CanonicalUrl" : "asd",
    "Index" : "No Index",
    "ImagePath" : "",
    "Position" : 0,
    "AuthorID" : ObjectId("5ecbae9f1bfe823e940e12a0"),
    "AuthorName" : "Admin",
    "SearchTerm" : "",
    "isPublish" : "0",
    "isDeleted" : false,
    "AddedOn" : ISODate("2020-06-08T09:39:34.007Z"),
    "LastModifiedOn" : ISODate("2020-06-08T09:39:34.007Z"),
    "__v" : 0
}

Below is my category table from which I want to fetch category name

{
    "_id" : ObjectId("5edb65a834c5cc735a76b8e4"),
    "Name" : "Cat 1",
    "ParentID" : "0",
    "IsDeleted" : false,
    "AddedOn" : ISODate("2020-06-06T09:45:12.632Z"),
    "LastModifiedOn" : ISODate("2020-06-06T09:45:12.632Z"),
    "__v" : 0
}

So, I want to get the records from "TEST" table also want to join with the "Category" Table to get the comma separated Category name which are in "TEST" Table in Array format.

I tried below query, but it is working for only when there is single record in "CategoryID" array. And if I send multiple records in categoryID array, then it gives same record multiple times with different category.

const getData = await TESTModel.aggregate([
    {
        $lookup: {
            from: "Category",       // other table name (Admin Table)
            localField: "CategoryID",   // name of blog table field
            foreignField: "_id", // name of Admin table field
            as: "testdata"         // alias for userinfo table
        },
    },
    {
        $unwind: "$testdata",
        $unwind: {
            path: "$testdata",
            preserveNullAndEmptyArrays: true
        }
    },
])

So, how can I get the Comma separated Category name in the query result.

The fields you are trying to "join" with in the `category` and `TEST` collection are to be of same data type (one of them is ObjectId and another is a String in an array). They need to be of same type. You can use aggregation operator [$convert](https://docs.mongodb.com/manual/reference/operator/aggregation/convert/index.html) to convert ObjectId to String and then compare. `$lookup` has a [variation](https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/index.html#specify-multiple-join-conditions-with-lookup) where you can specify a _pipeline_ for matching (with conversion). — prasad_, Jun 09 '20 at 11:01
You may want to try and tell what problem you are facing. The lookup's pipeline allows using `$match` stage in which you can compare the String array with the String version of the ObjectId. There are examples in the provided links. See this post: [Mongodb Join on _id field from String to ObjectId](https://stackoverflow.com/questions/41093647/mongodb-join-on-id-field-from-string-to-objectid). — prasad_, Jun 09 '20 at 11:12

score 2 · Accepted Answer · answered Jun 09 '20 at 11:42

2

Try with below code.

const data = TESTModel.aggregate([
       {
           $lookup:{
             from: "blogCategory",
             localField: "CategoryID",
             foreignField: "_id",
             as: "enrollee_info"
          }
       }
])

answered Jun 09 '20 at 11:42

Deepali Jadhav

540
2
8
18

How to join in mongo query with Array of IDs?

1 Answers1