How to join mongodb document on itself-CodePudding

I have a document

{
   propertyA: [
      { name: 'jim', group: 'a'},
      { name: 'bob', group: 'a'},
      { name: 'alice', group: 'b'},
   ],
   propertyB: [
      { group: 'a', numberOfTimes: 2 }
      { group: 'b', numberOfTimes: 6 }
   ]
}

What I'd like to be able to achieve with an aggregate would be

{
    result: [
      { name: 'jim', group: 'a', numberOfTimes: 2},
      { name: 'bob', group: 'a', numberOfTimes: 2},
      { name: 'alice', group: 'b', numberOfTimes: 6},
    ]
}

A bit stuck as not even sure what to search for...any help would be appreciated.

CodePudding user response：

Query

solution doesn't use lookup or unwind etc
it does it in document level
for each member of "propertyA" find the from propertyB the group and get the numberOfTimes
merge objects to add the numberOfTimes

*the above is made assuming that all groups exists in both propertyA and in PropertyB.(like in your sample example) If you have groups that exists only in 1 of those properties, if you can tell us how to handle them

Test Code here

db.collection.aggregate([
  {
    "$project": {
      "result": {
        "$map": {
          "input": "$propertyA",
          "in": {
            "$let": {
              "vars": {
                "nt": {
                  "$filter": {
                    "input": "$propertyB",
                    "cond": {
                      "$eq": [
                        "$$pb.group",
                        "$$pa.group"
                      ]
                    },
                    "as": "pb"
                  }
                }
              },
              "in": {
                "$mergeObjects": [
                  "$$pa",
                  {
                    "$arrayElemAt": [
                      "$$nt",
                      0
                    ]
                  }
                ]
              }
            }
          },
          "as": "pa"
        }
      },
      "_id": 0
    }
  }
])