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 to convert ObjectId to String and then compare. $lookup has a variation where you can specify a pipeline for matching (with conversion). — prasad_
– prasad_, Commented Jun 9, 2020 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. — prasad_
– prasad_, Commented Jun 9, 2020 at 11:12

Deepali Jadhav · Accepted Answer · 2020-06-09 11:42:06Z

2

Try with below code.

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

answered Jun 9, 2020 at 11:42

Deepali Jadhav

6002 gold badges8 silver badges18 bronze badges

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

Collectives™ on Stack Overflow

How to join in mongo query with Array of IDs?

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related