I have the two collection.

I need to join they and return only few fields. As I understand, I should use an aggregate and projection features. I try find examples, but can't find the right, in my case foreign key placed in the array in the nested field.

It is not easy for me, I’m new at this, and I couldn’t win against this mongoDb query.

In the code block below I wrote the simplified equivalent models (doc1, doc2) and expected results.

Well-respected mongodb gurus, does anybody have an ideas?

db.doc1.deleteMany({});
db.doc2.deleteMany({});

// tow related documents

db.doc1.insert( [
    { 
    "version" : 123456,
    "doc" : {
      "code":"A1",
      "name":"some document A1",
      "doc2CodeArray":[
          {"code":"B01"},
          {"code":"B02"},
          {"code":"B03"},
          {"code":"B04"},
          {"code":"B05"},
          {"code":"B06"}
          ]
      }
    },
    { 
    "version" : 123457,
    "doc" : {
      "code":"A2",
      "name":"some document A2",
      "doc2CodeArray":[
          {"code":"B07"},
          {"code":"B08"},
          {"code":"B09"},
          {"code":"B10"},
          {"code":"B11"},
          {"code":"B12"}
          ]
      }
    }    

]);

db.doc2.insert( [
    {
        "version" : 567890,
        "doc" : { 
          "code":"B01", 
          "valueArray":[{"valueType":"int","valueData":"1"}],
          "doc2Type":{"code":"C1"}
        } 
    },
    {"version" : 567890,"doc" : { "code":"B02", "valueArray":[{"valueType":"int","valueData":"2"}],"doc2Type":{"code":"C2"}}},
    {"version" : 567890,"doc" : { "code":"B03", "valueArray":[{"valueType":"int","valueData":"3"}],"doc2Type":{"code":"C3"}}},
    {"version" : 567890,"doc" : { "code":"B04", "valueArray":[{"valueType":"int","valueData":"4"}],"doc2Type":{"code":"C4"}}},
    {"version" : 567890,"doc" : { "code":"B05", "valueArray":[{"valueType":"int","valueData":"5"}],"doc2Type":{"code":"C5"}}},
    {"version" : 567890,"doc" : { "code":"B06", "valueArray":[{"valueType":"int","valueData":"6"},
                                                              {"valueType":"str","valueData":"F"}],"doc2Type":{"code":"C6"}}},
    
    {"version" : 567890,"doc" : { "code":"B07", "valueArray":[{"valueType":"int","valueData":"1"}],"doc2Type":{"code":"C1"}}},
    {"version" : 567890,"doc" : { "code":"B08", "valueArray":[{"valueType":"int","valueData":"2"}],"doc2Type":{"code":"C2"}}},
    {"version" : 567890,"doc" : { "code":"B09", "valueArray":[{"valueType":"int","valueData":"3"}],"doc2Type":{"code":"C3"}}},
    {"version" : 567890,"doc" : { "code":"B10", "valueArray":[{"valueType":"int","valueData":"4"}],"doc2Type":{"code":"C4"}}},
    {"version" : 567890,"doc" : { "code":"B11", "valueArray":[{"valueType":"int","valueData":"5"}],"doc2Type":{"code":"C5"}}},
    {"version" : 567890,"doc" : { "code":"B12", "valueArray":[{"valueType":"int","valueData":"6"}],"doc2Type":{"code":"C6"}}},    
    ]);


/*
  the result that I want

  {
      "doc1Code":"A1",
      "doc1Name":"some document A1",
      "doc2Items":[
        {"doc2TypeCode":"C1", "doc2ValueArray":[{"valueType":"int", "valueData":"1"}]}
        {"doc2TypeCode":"C2", "doc2ValueArray":[{"valueType":"int", "valueData":"2"}]}
        {"doc2TypeCode":"C3", "doc2ValueArray":[{"valueType":"int", "valueData":"3"}]}
        {"doc2TypeCode":"C4", "doc2ValueArray":[{"valueType":"int", "valueData":"4"}]}
        {"doc2TypeCode":"C5", "doc2ValueArray":[{"valueType":"int", "valueData":"5"}]}
        {"doc2TypeCode":"C6", "doc2ValueArray":[{"valueType":"int", "valueData":"6"},{"valueType":"str", "valueData":"F"}]}
      ]
  },
  {
      "doc1Code":"A2",
      "doc1Name":"some document A2",
      "doc2Items":[
        {"doc2TypeCode":"C1", "doc2ValueArray":[{"valueType":"int", "valueData":"1"}]}
        {"doc2TypeCode":"C2", "doc2ValueArray":[{"valueType":"int", "valueData":"2"}]}
        {"doc2TypeCode":"C3", "doc2ValueArray":[{"valueType":"int", "valueData":"3"}]}
        {"doc2TypeCode":"C4", "doc2ValueArray":[{"valueType":"int", "valueData":"4"}]}
        {"doc2TypeCode":"C5", "doc2ValueArray":[{"valueType":"int", "valueData":"5"}]}
        {"doc2TypeCode":"C6", "doc2ValueArray":[{"valueType":"int", "valueData":"6"}]}
      ]
  }

*/

CodePudding user response：

1. $lookup - Join doc1 with doc2.

   Pipeline:

   1.1. $match - Matching doc.code (from doc2) is within the doc.doc2CodeArray.code array (from doc1).

   1.2. $project - Decorate output document to be returned in doc2Items.

2. $project - Decorate output document.

db.doc1.aggregate([
  {
    $lookup: {
      from: "doc2",
      let: {
        doc2CodeArray: "$doc.doc2CodeArray.code"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $in: [
                "$doc.code",
                "$$doc2CodeArray"
              ]
            }
          }
        },
        {
          $project: {
            _id: 0,
            "doc2TypeCode": "$doc.doc2Type.code",
            "doc2ValueArray": "$doc.valueArray"
          }
        }
      ],
      as: "doc2Items"
    }
  },
  {
    $project: {
      _id: 0,
      "doc1Code": "$doc.code",
      "doc1Name": "$doc.name",
      "doc2Items": "$doc2Items"
    }
  }
])

Sample Mongo Playground

CodePudding user response：

https://mongoplayground.net/p/1-1SU8SgbTQ

db.doc1.aggregate([
  {
    $lookup: {
      from: "doc2",
      localField: "doc.doc2CodeArray.code",
      foreignField: "doc.code",
      as: "doc.doc2Items"
    }
  },
  {
    $replaceRoot: {
      newRoot: "$doc"
    }
  },
  {
    $addFields: {
      "doc2Items": {
        $map: {
          input: "$doc2Items",
          in: {
            doc2TypeCode: "$$this.doc.doc2Type.code",
            doc2ValueArray: "$$this.doc.valueArray"
          }
        }
      }
    }
  },
  {
    $unset: [
      "doc2CodeArray"
    ]
  }
])