CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
jcardoso
jcardoso

Reputation: 61

Join on Related properties of two Arrays

I have the following result. Below it would be possible to make a map joining the array each with its objectid.

{
    "_id": ObjectId("597233b50e717e0585dbd94a"),
    "createdAt": ISODate("2017-07-21T17:02:45.119+0000"),
    "name": "cardoso",
    "gender": "female",
    "profile": [{
            "profession": "master",
            "_id": ObjectId("597233b50e717e0585dbd94b"),
            "departament": ObjectId("597233b50e717e0585dbd94e")
        },
        {
            "_id": ObjectId("59766719003e7d552fe40e8c"),
            "profession": "master",
            "departament": ObjectId("59766719003e7d552fe40e8b")
        },
        {
            "_id": ObjectId("5976b8f99d8a4326c6bf1ae5"),
            "profession": "master",
            "departament": ObjectId("5974d8fe398e5b2fd433410f")
        }
    ],
    "Institution": {
        "_id": ObjectId("597233b50e717e0585dbd94c"),
        "cnpj": 64837134000144.0,
        "deletedAt": false,
        "departament": [{
                "title": "cardoso",
                "category": "Sub-17",
                "_id": ObjectId("597233b50e717e0585dbd94e")
            },
            {
                "sport": "Tênis",
                "title": "novo",
                "category": "Sub-12",
                "_id": ObjectId("59766719003e7d552fe40e8b")
            },
            {
                "_id": ObjectId("5974d8fe398e5b2fd433410f"),
                "category": "Intercâmbio",
                "title": "testeJJJ",
                "sport": "natação"
            }
        ]
    }
}

I need to do the following result. I did not want to have to manipulate the result in the node.

{
    "sport": "Tênis",
    "profession": "master",
    "title": "novo",
    "category": "Sub-12",
    "_id": ObjectId("59766719003e7d552fe40e8b")
}

I already tended to do something but the query ends up getting very big

Upvotes: 2

Views: 49

Answers (1)

Neil Lunn
Neil Lunn

Reputation: 151170

The basic premise here is to "lookup" the content in the other array whilst processing via $map.

This is either done via $indexOfArray with MongoDB 3.4:

db.collection.aggregate([
  { "$addFields": {
    "Institution": {
      "departament": {
        "$map": {
          "input": "$Institution.departament",
          "as": "d",
          "in": {
            "sport": "$$d.title",
            "profession": {
              "$arrayElemAt": [
                "$profile.profession",
                { "$indexOfArray": [ "$profile.departament", "$$d._id" ] }
              ]    
            },
            "title": "$$d.title",
            "category": "$$d.category"   
          } 
        }
      }
    }    
  }}
])

In that first index we look for the "index position" from the "profile" array that matches the current value of _id on the specified field. Then extract the data at that index via $arrayElemAt.

Or using $filter and the $arrayElemAt "the other way around" with MongoDB 3.2:

db.collection.aggregate([
  { "$addFields": {
    "Institution": {
      "departament": {
        "$map": {
          "input": "$Institution.departament",
          "as": "d",
          "in": {
            "sport": "$$d.title",
            "profession": {
              "$arrayElemAt": [
                { "$map": {
                  "input": {
                    "$filter": {
                      "input": "$profile",
                      "as": "p",
                      "cond": { "$eq": [ "$$p.departament", "$$d._id" ] }                    
                    }
                  },
                  "as": "p",
                  "in": "$$p.profession"
                }},
                0
              ]
            },
            "title": "$$d.title",
            "category": "$$d.category"   
          } 
        }
      }
    }    
  }}
])

In which case the $filter reduces the array content in "profile" down to only matching elements, which should be just one. This is then extracted at the 0 index by $arrayElemAt.

Same result in either case:

{
    "_id" : ObjectId("597233b50e717e0585dbd94a"),
    "createdAt" : ISODate("2017-07-21T17:02:45.119Z"),
    "name" : "cardoso",
    "gender" : "female",
    "profile" : [ 
        {
            "profession" : "master",
            "_id" : ObjectId("597233b50e717e0585dbd94b"),
            "departament" : ObjectId("597233b50e717e0585dbd94e")
        }, 
        {
            "_id" : ObjectId("59766719003e7d552fe40e8c"),
            "profession" : "master",
            "departament" : ObjectId("59766719003e7d552fe40e8b")
        }, 
        {
            "_id" : ObjectId("5976b8f99d8a4326c6bf1ae5"),
            "profession" : "master",
            "departament" : ObjectId("5974d8fe398e5b2fd433410f")
        }
    ],
    "Institution" : {
        "_id" : ObjectId("597233b50e717e0585dbd94c"),
        "cnpj" : 64837134000144.0,
        "deletedAt" : false,
        "departament" : [ 
            {
                "sport" : "cardoso",
                "profession" : "master",
                "title" : "cardoso",
                "category" : "Sub-17"
            }, 
            {
                "sport" : "novo",
                "profession" : "master",
                "title" : "novo",
                "category" : "Sub-12"
            }, 
            {
                "sport" : "testeJJJ",
                "profession" : "master",
                "title" : "testeJJJ",
                "category" : "Intercâmbio"
            }
        ]
    }
}

Upvotes: 1

Related Questions