$lookup and replace array of ids with values

Question

Consider below Mongo DB 3.2 Document,

Clients Collection Document

{
    "_id" : "gcJk4eRRo2WCbgWSL",
    "services" : [ 
        "2tLX8ALYfRvbgiurZ", 
        "wfE5MqgHfu9QKtK7d", 
        "MEZtABSEeskuRivXJ"
    ]
}

Also, Services Collection Documents

{ "_id" : "2tLX8ALYfRvbgiurZ", "name" : "GSTR 1" }
{ "_id" : "wfE5MqgHfu9QKtK7d", "name" : "GSTR 2" }
{ "_id" : "MEZtABSEeskuRivXJ", "name" : "GSTR 3" }

Now, the values in services array field in Clients is associated to _id of Services Collection.

Below is the code that I am currently executing,

db.getCollection('Clients').aggregate(
      [
        { "$unwind" : { path: "$services"}},
        {
            "$lookup" : { from: "Services", localField: "services", foreignField: "_id", as: "services" }
        },
        { "$unwind" : { path: "$services", preserveNullAndEmptyArrays: true }},
        { "$project" : {
                          _id : 1, services: '$services.name' 
                       }
        }
      ]
    );

Output of Above code execution is,

{ "_id" : "gcJk4eRRo2WCbgWSL", "services" : "GSTR 1" }
{ "_id" : "gcJk4eRRo2WCbgWSL", "services" : "GSTR 2" }
{ "_id" : "gcJk4eRRo2WCbgWSL", "services" : "GSTR 3" }

But Expected output is as below,

{
    "_id" : "gcJk4eRRo2WCbgWSL",
    "services" : "GSTR 1, GSTR 2, GSTR 3"
}

Any help is highly appreciated.

mickl · Accepted Answer · 2017-11-27 16:51:18Z

2

You can add additional $group by your _id with $push to merge your services into one array.

db.Clients.aggregate(
  [
    { "$unwind" : { path: "$services"} },
    {
        "$lookup" : { from: "Services", localField: "services", foreignField: "_id", as: "services" }
    },
    { "$unwind" : { path: "$services", preserveNullAndEmptyArrays: true }},
    { "$project" : {
                      _id : 1, services: '$services.name' 
                   }
    },
    {
      "$group": {
        _id: "$_id",
        "services": { "$push": "$services"}
      }
    }
  ]
);

answered Nov 27, 2017 at 16:51

mickl

50.1k9 gold badges71 silver badges99 bronze badges

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

1 Comment

Ankur Soni Over a year ago

I appreciate your help, I have upvoted too, but I have changed the expected output, I did a mistake before. Thanks a lot.

dnickless · Accepted Answer · 2017-11-29 22:55:10Z

Here's how you do that - there is no need for any $unwinds as long as you do not care about the item order inside your string.

db.getCollection('Clients').aggregate([
{
    "$lookup": {
        from: "Services",
        localField: "services",
        foreignField: "_id",
        as: "services"
    }
}, {
    "$project": {
        "_id" : 1,
        "services": {
            $substr:
            [
                {
                    $reduce: { // transform array of strings into concatenated string
                        input: '$services.name',
                        initialValue: '',
                        in: {
                            $concat: ['$$value', ', ', '$$this']
                        }
                    }
                },
                2, // remove first two characters as they will be ', '
                -1
            ]
        }
    }
}])

This, however, can potentially return something like the following document (note the order of the entries in the string):

{
    "_id" : "gcJk4eRRo2WCbgWSL",
    "services" : "GSTR 1, GSTR 3, GSTR 2"
}

If you need the items inside the string to be sorted you can do it this way:

db.getCollection('Clients').aggregate([
{
    "$lookup": {
        from: "Services",
        localField: "services",
        foreignField: "_id",
        as: "services"
    }
}, {
    $unwind: "$services" // flatten the services array
}, {
    $sort: {
        "services.name": 1 // sort all documents by service name
    }
}, {
    $group: { // group the everything back into the original structure again
        "_id": "$_id", // we want one group per document id
        "services": {
            $push: "$services" // and all its services in an array - this time reliably sorted!
        }
    }
}, {
    "$project": {
        "_id": 1,
        "services": {
            $substr:
            [
                {
                    $reduce: { // transform array of strings into concatenated string
                        input: '$services.name',
                        initialValue: '',
                        in: {
                            $concat: ['$$value', ', ', '$$this']
                        }
                    }
                },
                2, // remove first two characters as they will be ', '
                -1
            ]
        }
    }
}])

This way you reliably get the entries sorted by name:

{
    "_id" : "gcJk4eRRo2WCbgWSL",
    "services" : "GSTR 1, GSTR 2, GSTR 3"
}

dundun · Accepted Answer · 2017-11-28 10:51:05Z

1

You can try using concatArrays.

Like what mickl answered, you can do grouping first and project it to concat the array value.

//previous code
{
  "$group": {
    _id: "$_id",
    "services": { "$push": "$services"}
  }
},{ $project: { 
      _id : "$_id",
      "services": { $concatArrays: [ "$services" ] } } 
}

hope it helps..

answered Nov 28, 2017 at 10:51

dundun

504 bronze badges

Collectives™ on Stack Overflow

$lookup and replace array of ids with values

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related