How to display name with the id but with datas in 2 collections MongoDb?

Question

I just want change a Id by thea name corresponding. But id is in a collection and name in other collection. "$lookup" Mongo doesn't work in my case...

The first collection "parameter" contains items with the "category_id":

{"_id" : ObjectId("56cc8827b9e4ed0fd42a4569"), 
    "data" : {
        "capacity" : NumberInt(60), 
        "categories" : [
            {
                "category_id" : "5964961294ff4a37988e8f9b", 
                "nbMax" : NumberInt(1), 
                "nbRes" : NumberInt(0)
            }, 
            {
                "category_id" : "596495c994ff4a37988e8f99", 
                "nbMax" : NumberInt(1), 
                "nbRes" : NumberInt(0)
            }, 
        ], 
      }, 
    "type" : "launcher", 
    "name" : "launcherp01", 
    "description" : "", 
    "_class" : "parameter"
}
....

{ 
    "_id" : ObjectId("56cc8827b9e4ed0fd42a4847"),
    "data" : {
        "capacity" : NumberInt(60), 
        "categories" : [
            {
                "category_id" : "596495c994ff4a37988e8f99", 
                "nbMax" : NumberInt(1), 
                "nbRes" : NumberInt(0)
            }, 
            {
                "category_id" : "8864961294ff4a37988e8f3b", 
                "nbMax" : NumberInt(1), 
                "nbRes" : NumberInt(0)
            }, 
        ], 
      }, 
    "type" : "launcher", 
    "name" : "launcherp01", 
    "description" : "", 
    "_class" : "parameter"
}
.....

The second Collection "reference" contains the description of categories with the _id (same as category_id in first collection) and the name:

{
   "_id" : ObjectId("596495c994ff4a37988e8f99"), 
    "taskType" : "qc", 
    "type" : "category", 
    "name" : "**qcSupportNormal01**", 
    "_class" : "reference", 
}

{ 
    "_id" : ObjectId("5964961294ff4a37988e8f9b"),
    "taskType" : "transcode", 
    "type" : "category", 
    "name" : "tsSupportNormal01", 
    "_class" : "reference", 
}

I want something like that (item with categories names not id):

  { 
        "_id" : ObjectId("56cc8827b9e4ed0fd42a4569"), 
        "type" : "launcher", 
        "categories" : [
            "qcSupportNormal01", //--->> name from reference collection
            "tsSupportNormal01", //--->> name from reference collection
    }     

    { 
        "_id" : ObjectId("56cc8827b9e4ed0fd42a4847"), 
        "type" : "launcher", 
        "categories" : [
            "qcSupportNormal01", //--->> name from reference collection
            "qptestNormal01",    //--->> name from reference collection
    ...

My query:

db.parameters.aggregate([
///////////////////////item filter
    {$match: {
      type:{ $in: [ "launcher" ] } ,
    }},  
///////////////////// foreign field      
    {$lookup: {
            from: "references",
            localField: "categories",
            foreignField: "_id",
            as: "references"
    }},
/////////////////// projection 
    {$project:
        {_id:1,type:1,categories:"$data.categories.category_id"
    }},
])

but the result is always with id not the name:

{ 
    "_id" : ObjectId("56cc8827b9e4ed0fd42a4569"), 
    "type" : "launcher", 
    "categories" : [
        "5964961294ff4a37988e8f9b", //--->> id not name from reference collection !
        "596495c994ff4a37988e8f99", //--->> id not name from reference collection !
       ...

How to have the category name not the id. Very simple in Sql (joint with foreign key and reference table) but complex in Mongo query ...

Thanks for your help

je déteste le langage Mongo !

Answer 1

There are a couple of issues with your approach:

1. You are trying to call $lookup between an ObjectId and a string value.
2. For your categories field you are mapping it with the category_id from the data.categories whereas it should have come from references.name.
3. The field category_id is nested inside of an array and when you are calling the $lookup you are matching it with the whole array instead of the key field.

So, now the best approach would be to convert the field category_id to an ObjectId and then call the $lookup for the field(instead of modifying the actual field i have appended a new field named as cat_id).

Here is how i would do it:

db.parameter.aggregate([    
{
    $match: {
        type: { $in: ["launcher"] },
    }
},
{ $unwind: "$data.categories" },
{ $addFields: { "data.categories.cat_id": { $convert: { input: "$data.categories.category_id", to: "objectId" } } } },
{
    $lookup: {
        from: "references",
        localField: "data.categories.cat_id",
        foreignField: "_id",
        as: "ref"
    }
},
{
    $project:
        {
            _id: 1, type: 1, categories: "$ref.name"
        }
}
]);

Which will give you an output as:

{
"_id" : ObjectId("56cc8827b9e4ed0fd42a4569"),
"type" : "launcher",
"categories" : [
    "tsSupportNormal01"
]
},
{
"_id" : ObjectId("56cc8827b9e4ed0fd42a4569"),
"type" : "launcher",
"categories" : [
    "**qcSupportNormal01**"
]
},
{
"_id" : ObjectId("56cc8827b9e4ed0fd42a4847"),
"type" : "launcher",
"categories" : [
    "**qcSupportNormal01**"
]
},
{
"_id" : ObjectId("56cc8827b9e4ed0fd42a4847"),
"type" : "launcher",
"categories" : [ ]
}