Mongodb subdocument array lookup to subdocument array

Question

I am new to mongo. i have following collections.

Document:

{
    "_id" : ObjectId("5eb256c9e519051af2eeb5f7"),
    "name" : "Income",
    "types" : [ 
        {
            "typeId" : ObjectId("5eb257a3e519051af2eeb624"),
            "name" : "W2",
            "tenantId" : null,
            "message" : "",
            "createdOn" : null,
            "createdBy" : 1.0,
            "isActive" : true
        }, 
        {
            "typeId" : ObjectId("5eb257a3e519051af2eeb639"),
            "name" : "Salary Slip",
            "tenantId" : 1,
            "message" : "",
            "createdOn" : null,
            "createdBy" : 1.0,
            "isActive" : true
        }
    ]
}

Request:

{
    "_id" : ObjectId("5eb25d1fe519051af2eeb72d"),
    "employeeId" : 1234,
    "customerId" : 1275,
    "tenantId" : 1,
    "createdOn" : ISODate("2013-10-01T00:00:00.000Z"),
    "loanApplicationId" : 1.0,
    "status" : "requested",
    "message" : "Dear John, please send following documents.",
    "documents" : [ 
        {
            "typeId" : null,
            "displayName" : "W2 2016",
            "message" : "please upload salary slip for year 2016",
            "status" : "requested",
            "files" : []
        }, 
        {
            "typeId" : ObjectId("5eb257a3e519051af2eeb624"),
            "displayName" : "W2 2016",
            "message" : "please upload salary slip for year 2016",
            "status" : "requested",
            "files" : []
        }
    ]
}

typeId in the document collection is the id for the document type where typeId in the request is the foreign field that can be nullable as well. how can i get the below output.

{
    "_id" : ObjectId("5eb25d1fe519051af2eeb72d"),
    "employeeId" : 1234,
    "customerId" : 1275,
    "tenantId" : 1,
    "createdOn" : ISODate("2013-10-01T00:00:00.000Z"),
    "loanApplicationId" : 1.0,
    "status" : "requested",
    "message" : "Dear John, please send following documents.",
    "documents" : [ 
        {
            "typeId" : null,
            "typeInfo": null,
            "displayName" : "W2 2016",
            "message" : "please upload salary slip for year 2016",
            "status" : "requested",
            "files" : []
        }, 
        {
            "typeId" : ObjectId("5eb257a3e519051af2eeb624"),
            "typeInfo": {
            "name": "W2" 
            },
            "displayName" : "W2 2016",
            "message" : "please upload salary slip for year 2016",
            "status" : "requested",
            "files" : []
        }
    ]
}

ngShravil.py · Accepted Answer · 2020-05-13 19:38:26Z

I loved to solve this problem, it was a tricky, long and complex. Well, I am also a beginner, so I used all the basic concepts. You can try the below code:

db.request.aggregate([
  {
    $unwind: "$documents"
  },
  {
    $lookup: {
      from: "document",
      let: {
        req_typeId: "$documents.typeId"
      },
      pipeline: [
        {
          $unwind: "$types"
        },
        {
          $match: {
            $expr: {
              $eq: [
                "$$req_typeId",
                "$types.typeId"
              ]
            }
          }
        }
      ],
      as: "documents.example"
    }
  },
  {
    $unwind: {
      path: "$documents.example",
      preserveNullAndEmptyArrays: true
    }
  },
  {
    $group: {
      _id: "$_id",
      employeeId: {
        $first: "$employeeId"
      },
      customerId: {
        $first: "$customerId"
      },
      tenantId: {
        $first: "$tenantId"
      },
      createdOn: {
        $first: "$createdOn"
      },
      loanApplicationId: {
        $first: "$loanApplicationId"
      },
      status: {
        $first: "$status"
      },
      message: {
        $first: "$message"
      },
      documents: {
        $push: {
          typeId: "$documents.typeId",
          typeInfo: {
            $cond: [
              {
                $eq: [
                  "$documents.example",
                  undefined
                ]
              },
              null,
              {
                name: "$documents.example.types.name"
              }
            ]
          },
          displayName: "$documents.displayName",
          message: "$documents.message",
          status: "$documents.status",
          files: "$documents.files"
        }
      }
    }
  }
])

If you get stuck at any point, I have provided MongoPlayGround Link, here you can execute the query stage by stage of the aggregation pipeline, else reach out for me. It's a long query, it will take time to understand. At the same time, I will be trying to shrink it. If I get anything, I will update it here.

Edits:

I have shortened the query by almost 50% (in terms of lines). Here is my previous approach's link

Valijon · Accepted Answer · 2020-05-13 12:04:09Z

With the default $lookup syntax, you can join with atomic / array values and store the result inside tmp variable. In the next step, we iterate over documents field and find within tmp variable items with the same typeId.

Note: If you need more fields than name, just change to {typeInfo: "$$this"}.

Try this one:

db.Requests.aggregate([
  {
    $lookup: {
      from: "Documents",
      localField: "documents.typeId",
      foreignField: "types.typeId",
      as: "tmp"
    }
  },
  {
    $addFields: {
      tmp: "$$REMOVE",
      documents: {
        $map: {
          input: "$documents",
          as: "doc",
          in: {
            $mergeObjects: [
              "$$doc",
              {
                $reduce: {
                  input: {
                    $reduce: {
                      input: "$tmp.types",
                      initialValue: [],
                      in: {
                        $concatArrays: [
                          "$$value",
                          "$$this"
                        ]
                      }
                    }
                  },
                  initialValue: {},
                  in: {
                    $cond: [
                      {
                        $eq: [
                          "$$this.typeId",
                          "$$doc.typeId"
                        ]
                      },
                      {
                        typeInfo: {
                          name: "$$this.name"
                        }
                      },
                      "$$value"
                    ]
                  }
                }
              }
            ]
          }
        }
      }
    }
  }
])

MongoPlayground

Collectives™ on Stack Overflow

Mongodb subdocument array lookup to subdocument array

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related