I have three collections, Posts, Languages and Translations. And the structure looks like this:

Post: {
    id: ObjectID
    title: String,
    translated_versions: ObjectID
}

TranslatedVersion: {
    id: ObjectID
    translations: Translation[]
}

// Just an object structure in TranslatedVersion.translations, not collection
Translation: {
    post: ObjectID
    language: ObjectID
}

Language {
    id: ObjectID
    name: String
}

What I want to do is to get entry from translated versions collections, put it into my post and then for every entry in TranslatedVersion.translation I want to get post and language objects, so it will look like this:

Post {
    title: "Test title",
    translated_versions: {
        id: 'some-id',
        tranlations: [
            Translation {
                language: {
                    id: 'some-id',
                    name: 'Turkish'
                },
                post: {
                    id: 'some-id'
                    title: 'Turkish post'
                }
            },
            Translation {
                language: {
                    id: 'some-id',
                    name: 'English'
                },
                post: {
                    id: 'some-id',
                    title: 'English post'
                }
            }
        ]
    }
}

I am using Golang and I tried to do it this way (sorry lots of lines of code):

    lookup_translations_group := bson.D{
        {
            Key: "$lookup", Value: bson.D{
                {
                    Key: "from", Value: Models.Translation,
                },
                {
                    Key: "localField", Value: "translations_group_id",
                },
                {
                    Key: "foreignField", Value: "_id",
                },
                {
                    Key: "as", Value: "translations",
                },
            },
        },
    }

    unwind_translations_group := bson.D{
        {
            Key: "$unwind", Value: bson.D{
                {
                    Key: "path", Value: "$translations",
                },
                {
                    Key: "preserveNullAndEmptyArrays", Value: true,
                },
            },
        },
    }

    lookup_translations_posts := bson.D{
        {
            Key: "$lookup", Value: bson.D{
                {
                    Key: "from", Value: Models.Post,
                },
                {
                    Key: "localField", Value: "translations.translations.post",
                },
                {
                    Key: "foreignField", Value: "_id",
                },
                {
                    Key: "as", Value: "translations.translations.post",
                },
            },
        },
    }

Then I run mongo.Pipeline and what I get is an object with "post" field which is an array with my translated posts:

So obviously I am doing something wrong here :( I assume I need to add another pipeline in lookup_translations_group stage, but I have no idea how to it correctly.

upd2: Even more complex examples:

db.posts.insertMany([
   { "_id" : 1, "title": "English post", language: 1, category: 1, "translations": 1, },
   { "_id" : 2, "title": "Turkish post", langauge: 2, category: 1, "translations": 1, },
   { "_id" : 2, "title": "Finnish post", language: 3, category: 1, "translations": 1, },
])

db.languages.insertMany([
   { "_id" : 1, "name": "English", },
   { "_id" : 2, "name": "Turkish", },
   { "_id" : 3, "name": "Finnish", },
])

db.translations.insert({
   "_id" : 1, 
   translations: [
      { language: 1, post: 1 },
      { language: 2, post: 2 },
      { language: 3, post: 3 },
   ] 
})

db.categories.insert({
   "_id" : 1, 
   "name": "Random category"
})

Result that I want:

{
   "_id": 1,
   "title": "English post",
   "language": {
      "_id": 1,
      "name": "English"
   },
   "category": {
      "_id": 1,
      "name": "Random category"
   },
   "translations": {
      "_id": 1,
      translations: [
         {
            "language": {
               "_id": 1,
               "name": "English"
            },
            "post": {
               "_id": 1,
               "title": "English post",
               "category": {
                  "_id": 1,
                  "name": "Random category"
               }
            }
         },
         {
            "language": {
               "_id": 2,
               "name": "Turkish"
            },
            "post": {
               "_id": 2,
               "title": "Turkish post",
               "category": {
                  "_id": 1,
                  "name": "Random category"
               }
            }
         },
         {
            "language": {
               "_id": 3,
               "name": "Finnish"
            },
            "post": {
               "_id": 3,
               "title": "Finnish post",
               "category": {
                  "_id": 1,
                  "name": "Random category"
               }
            }
         },
      ]
   }
}

CodePudding user response：

If I understand correctly, you want something like:

db.translations.aggregate([
  {$match: {_id: 1}},
  {$lookup: {
      from: "posts",
      localField: "translations.post",
      foreignField: "_id",
      as: "posts"
  }},
  {$lookup: {
      from: "languages",
      localField: "posts.language",
      foreignField: "_id",
      as: "translations"
  }},
  {$project: {
      data: {$map: {
          input: "$posts",
          in: {
            post: {title: "$$this.title", _id: "$$this._id"},
            language: {$arrayElemAt: [
                "$translations",
                {$indexOfArray: ["$translations._id", "$$this._id"]}
            ]}
          }
      }}
  }},
  {$replaceRoot: {
      newRoot: {$mergeObjects: [
          {$first: {$filter: {input: "$data", cond: {$eq: ["$$this.post._id", "$_id"]}}}},
          {translations: {
              _id: "$_id",
              translations: {
                $filter: {input: "$data", cond: {$ne: ["$$this.post._id", "$_id"]}}
              }
          }}
      ]}
  }},
  {$project: {_id: "$post._id", title: "$post.title", language: 1, translations: 1}}
])

See how it works on the playground example