How to include another key in array object into array aggregate

Working with Data
1

I get difficult to add key into the aggregate from array object

here is the data which I want to aggregate

  // COMPANY
   {
     "name": "Note Inc",
     "shortName": "The Note",
     "members": [ 
        {
          "_id": ObjectId("5efdac62cd210f3b658d98f6"),
          "status": false,
          "role": "chairman",
        },
        {
          "_id": ObjectId("5effac62dd210f9b658d98f1"),
          "status": true,
          "role": "admin",
        }
     ]
   }

and the result I want

   // COMPANY after aggregate the Person
   {
     "name": "Note Inc",
     "shortName": "The Note",
     "members": [ 
        {
          "_id": ObjectId("5efdac62cd210f3b658d98f6"),
          "name": "Shinta",
          "gender": "female"
          "status": false,
          "role": "chairman",
        },
        {
          "_id": ObjectId("5effac62dd210f9b658d98f1"),
          "name": "John",
          "gender": "male"
          "status": true,
          "role": "admin",
        }
     ]
   }

in my case here I am using Go, but I need the vanilla Mongo Query for this so I can translate into Go query pipeline

2

Ok, in the output you want to have 2 additional fields: ‘name’ and ‘gender’.
Where are you going to take that data from? Is it from sibling collection? Provide a document example, if the answer is ‘yes’.

3

yess that output was from the Person ID , so the members._id is trigger to Person ID ( reference )
here is the document Person

{
    "_id": ObjectId("5effac62dd210f9b658d98f1"),
    "email": "test@admin.com",
    "phoneNumber": "",
    "firstName": "John",
    "gender": "male"
}
4

This aggregation should give you what you need:

db.members.aggregate([
  {
    $addFields: {
      ids: {
        $map: {
          input: '$members',
          in: '$$this._id',
        }
      }
    }
  },
  {
    // $lookup will be executed once per member document
    $lookup: {
      from: 'persons',
      localField: 'ids',
      foreignField: '_id',
      as: 'persons',
    }
  },
  // cleanup
  {
    $unset: ['ids'],
  },
  {
    $addFields: {
      mixed: {
        // order of array here matters for $group stage
        $concatArrays: ['$members', '$persons']
      }
    }
  },
  {
    $unwind: '$mixed',
  },
  {
    $group: {
      _id: {
        _id: '$mixed._id',
        rootId: '$_id',
        name: '$name',
        shortName: '$shortName',
      },
      // down below:
      // - use $first to read the prop from 'person' document
      // - use $last to read the prop from 'members' document
      name: {
        $last: '$mixed.firstName',
      },
      gender: {
        $last: '$mixed.gender',
      },
      status: {
        $first: '$mixed.status',
      },
      role: {
        $first: '$mixed.role',
      }
    }
  },
  {
    $group: {
      _id: '$_id.rootId',
      name: {
        $first: '$_id.name',
      },
      shortName: {
        $first: '$_id.shortName',
      },
      members: {
        $push: {
          _id: '$_id._id',
          name: '$name',
          gender: '$gender',
          status: '$status',
          role: '$role',
        }
      }
    }
  },
  // cleanup
  {
    $unset: '_id',
  }
]);

Tested on this dataset:

db.members.insertMany([
  {
    "name": "Note Inc A",
    "shortName": "The Note A",
    "members": [
      {
        "_id": ObjectId("5effac62dd210f9b658d98f1"),
        "status": false,
        "role": "chairman",
      },
      {
        "_id": ObjectId("5efdac62cd210f3b658d98f6"),
        "status": true,
        "role": "admin",
      }
    ]
  },
  {
    "name": "Note Inc B",
    "shortName": "The Note B",
    "members": [
      {
        "_id": ObjectId("5efdac62cd210f3b65f68d98"),
        "status": false,
        "role": "hr",
      },
    ]
  }
]);

db.persons.insertMany([
  {
    "_id": ObjectId("5effac62dd210f9b658d98f1"),
    "firstName": "John",
    "gender": "male",
  },
  {
    "_id": ObjectId("5efdac62cd210f3b658d98f6"),
    "firstName": "Shinta",
    "gender": "female",
  },
  {
    "_id": ObjectId("5efdac62cd210f3b65f68d98"),
    "firstName": "Sara",
    "gender": "female",
  },
]);
5

I updated the document

6

is this is can’t be done by $project ?

7

Simply using $project is not enough.

  • You need $lookup to join tables.
  • You need $unwind, $addFields, $group to restructure and merge your objects.

Here is the minimal aggregation, that you can have to get what you need:

db.members.aggregate([
  {
    $unwind: {
      path: '$members',
      preserveNullAndEmptyArrays: true,
    }
  },
  {
    $lookup: {
      from: 'persons',
      localField: 'members._id',
      foreignField: '_id',
      as: 'person',
    }
  },
  {
    $unwind: '$person',
  },
  {
    $addFields: {
      'members.name': '$person.firstName',
      'members.gender': '$person.gender',
    },
  },
  {
    $group: {
      _id: '$_id',
      name: {
        $first: '$name',
      },
      shortName: {
        $first: '$name',
      },
      members: {
        $push: '$members',
      }
    }
  }
]);

But this one will probably be less performant, as it will make $lookup per each item in ‘members’ array.

8

@slava, @Virtual_Database, I tried this aggregation. Let me know how it works for you.

db.company.aggregate([ 
  { 
      $lookup: { 
          from: "person", 
          localField: "members._id", 
          foreignField: "_id", 
          as: "company_persons" 
      } 
  }, 
  { 
      $addFields: { 
          company_persons: 0,
          members: { 
              $map: { 
                  input: "$members", as: "mem", 
                  in: {
                      $let: {
                           vars: { 
                               varin: { 
                                   $arrayElemAt: [ { $filter: {
                                                        input: "$company_persons", as: "per", 
                                                        cond: { $eq: [ "$$per._id", "$$mem._id" ] }
                                    } }, 0 ] 
                                } 
                           },
                           in: { 
                               $mergeObjects: [ "$$mem", {  firstName: "$$varin.firstName", gender: "$$varin.gender" } ]
                           }
                      }
                  }
              }
          }
      }
  }
]).pretty()
9

the latest minimal query was work well, what different ? will the latest minimal query gonna has affect ??

10

the previous query u show was , I got : (AtlasError) _id is not allowed in this atlas tier :frowning:

11

this one is worked also, btw, can I just filter one person id ?

like say I want just filter person where ID = ObjectId(“PERSON_ID”) so I dont want to show all members on the list

12

Yes, you can (with the same aggregation and some changes):

var INPUT_PERSON = ObjectId("5efdac62cd210f3b658d98f6") // or ObjectId("5effac62dd210f9b658d98f1")

db.company.aggregate( [ 
  { 
      $lookup: { 
          from: "person", 
          localField: "members._id", 
          foreignField: "_id", 
          as: "company_persons" 
      } 
  }, 
  { 
      $addFields: { 
          members: { 
              $reduce: { 
                  input: "$members", initialValue: [ ],
                  in: {
                      $let: {
                           vars: { 
                               match: { 
                                   $arrayElemAt: [ { $filter: {
                                                        input: "$company_persons", as: "per", 
                                                        cond: { $eq: [ "$$per._id", "$$this._id" ] }
                                    } }, 0 ] 
                                } 
                           },
                           in: { 
                               $cond: [ { $eq: [ INPUT_PERSON, "$$this._id" ] }, 
                                        [ { $mergeObjects: [ "$$this", { firstName: "$$match.firstName", gender: "$$match.gender" } ] } ],
                                        "$$value"
                               ]
                           }
                      }
                  }
              }
          }
      }
  },
  { 
      $project: { company_persons: 0 } 
  }
] ).pretty()
13

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.