How to lookup or aggregate a field of an object in an array of javascript objects (in mongodb)?

Question

I want have the field contractType in my mongoose schema, is an array of objects that each one has the property authorizedBy into the second level.

contractType field could have many items and I want to know each authorizedBy finding it in other collection called admins (I'll let it below too).

const professionalSchema = new Schema(
  firstName: String,
  contractType: [
    new Schema(
      {
        contract: String,
        authorizedBy: {
          type: ObjectId,
          ref: 'admin',
          index: true,
        },
        document: String
      },
      {
        timestamps: true,
        versionKey: false,
      },
    ),
  ],
)

this is my admin collection.

const adminSchema = new Schema(
  {
    firstName: String,
    lastName: String,
    role: String
  },
  {
    timestamps: true,
    versionKey: false,
  },
)

I have this in professionalSchema mongodb compass:

{
  "_id": ObjectId("6009a0d0874f0900086ee0ce"),
  "firstName": "xxxx",
  "gender": "NOT_SPECIFIED",
  "contractType": [
    {
      "authorizedBy": ObjectId("5fad90665d963cbbbd4a6580"),
      "document": "document 1"
    },
    {
      "authorizedBy": ObjectId("5fad90665d963cbbbd4a6580"),
      "document": "document 2"
    }
  ]
}

this is an admin of the collection of admins (adminSchema):

{
  "_id": ObjectId("5fad90665d963cbbbd4a6580"),
  "role": "SAM",
  "firstName": "firstname",
  "lastName": "lastname"
}

I would have response like this one below, additionally I want to get all fields of the object (20 aprox) not adding each one manually, like spread operator (...item) in javascript

{
  "_id": ObjectId("6009a0d0874f0900086ee0ce"),
  "firstName": "xxxx",
  "contractType": [
    {
      "authorizedBy": {
        "_id": "5fad90665d963cbbbd4a6580",
        "firstName": "firstname",
        "lastName": "lastname",
        "role": "SAM"
      },
      "document": "document 1"
    },
    {
      "authorizedBy": {
        "_id": "5fad90665d963cbbbd4a6580",
        "firstName": "firstname",
        "lastName": "lastname",
        "role": "SAM"
      },
      "document": "document 2"
    }
  ]
}

I tried this in mongo compass.

[
  {
    '$match': {
      '_id': new ObjectId('6009a0d0874f0900086ee0ce')
    }
  }, {
    '$unwind': {
      'path': '$contractType'
    }
  }, {
    '$lookup': {
      'from': 'admins', 
      'localField': 'contractType.authorizedBy', 
      'foreignField': '_id', 
      'as': 'contractType.authorizedBy'
    }
  }, {
    '$unwind': {
      'path': '$contractType.authorizedBy'
  }
  }, {
    $group': {
      '_id': '$_id'
    }
  }
]

but I don't know how put rest of elements (20 aprox) in the same object :(

Can you share example input and expected output? – J.F. Jan 22 '21 at 23:01 — J.F., Jan 22 '21 at 23:01
I update the question with that info :D – Cristobal Contreras Jan 22 '21 at 23:34 — Cristobal Contreras, Jan 22 '21 at 23:34

score 1 · Answer 1 · answered Jan 23 '21 at 05:14

What you have done is almost correct,

db.professionalSchema.aggregate([
  {"$match": {"_id": ObjectId("6009a0d0874f0900086ee0ce") } },
  {"$unwind": {"path": "$contractType" }},
  {
    "$lookup": {
      "from": "adminSchema",
      "localField": "contractType.authorizedBy",
      "foreignField": "_id",
      "as": "contractType.authorizedBy"
    }
  },
  {
    "$addFields": {
      "contractType.authorizedBy": {
        $ifNull: [
          {  $arrayElemAt: [ "$contractType.authorizedBy", 0 ] },
          ""
        ]
      }
    }
  },
  {
    "$group": {
      "_id": "$_id",
      "firstName": { $first: "$firstName" },
      "gender": { $first: "$gender"},
      contractType: { $push: "$contractType" }
    }
  }
])

Working Mongo playground

but if in my professionalSchema I have more than 20 fields and some of these have embedded document... Is there a way to pass all properties to the the result of group? like spread operator in javascript {...items} — Cristobal Contreras, Jan 24 '21 at 15:43

How to lookup or aggregate a field of an object in an array of javascript objects (in mongodb)?

1 Answers1