MongoDB double nested array to reserve unwind

Question

I have a double nested document and I need to $lookup it with the other collection. For that I unwind the document, then do the $lookup and I have struck here. I cannot 'un-unwind' it.

I am struck here:

mongoose.aggregate([
    { "$match": { "weekNumber": weekNumber } },
    { "$unwind": "$locations" },
    { "$addFields": { "shifts": "$locations.shifts"}},
    { "$unwind": "$shifts" },
    { "$lookup": {
            "let": { "userObjId": { "$toObjectId": "$shifts.shiftTypeId" } },
            "from": "shiftTypes",
            "pipeline": [
                { "$match" : { "$expr": { "$eq" : [ "$_id", "$$userObjId"] } } }
            ],
            "as": "shiftType"
        }
    },
    { "$addFields": {
            "shifts.name": "$shiftType.name",
            "shifts.color": "$shiftType.color"
        }
    },
    { "$project": {
            "shiftType": 0,
            "locations.shifts": 0
        }
    }
])

Currently I have unwinded object with all the necessary fields, but cannot 'pack' them back together.

I have a collections - planning - with the following:

[
  {
    _id: ObjectId(),
    "weekNumber": 30,
    "locations": [
      {
        "location": "locationName1",
        "shifts": [
          {
            "shiftTypeId": "shiftType001",
          },
          {
            "shiftTypeId": "shiftType002",
          },
          {
            "shiftTypeId": "shiftType001",
          }
        ]
      },
      {
        "location": "locationName2",
        "shifts": [
          {
            "shiftTypeId": "shiftType001",
          },
          {
            "shiftTypeId": "shiftType002",
          },
          {
            "shiftTypeId": "shiftType001",
          }
        ]
      },
      {
        "location": "locationName3",
        "shifts": [
          {
            "shiftTypeId": "shiftType001",
          },
          {
            "shiftTypeId": "shiftType002",
          },
          {
            "shiftTypeId": "shiftType001",
          }
        ]
      }
    ]
  }
]

and I have another collection - shiftTypes - like

[
  {
    _id: ObjectId("shiftType001"),
    "name": "shift001",
    "color": "red"
  },
  {
    _id: ObjectId("shiftType002"),
    "name": "shift002",
    "color": "blue"
  }
]

What I would like to achive is this:

[
  {
    _id: ObjectId(),
    "weekNumber": 30,
    "locations": [
      {
        "location": "locationName1",
        "shifts": [
          {
            "shiftTypeId": "shiftType001",
            "name": "shift001",
            "color": "red"
          },
          {
            "shiftTypeId": "shiftType002",
            "name": "shift002",
            "color": "blue"
          },
          {
            "shiftTypeId": "shiftType001",
            "name": "shift001",
            "color": "red"
          }
        ]
      },
      {
        "location": "locationName2",
        "shifts": [
          {
            "shiftTypeId": "shiftType002",
            "name": "shift002",
            "color": "blue"
          },
          {
            "shiftTypeId": "shiftType002",
            "name": "shift002",
            "color": "blue"
          },
          {
            "shiftTypeId": "shiftType001",
            "name": "shift001",
            "color": "red"
          }
        ]
      },
      {
        "location": "locationName3",
        "shifts": [
          {
            "shiftTypeId": "shiftType001",
            "name": "shift001",
            "color": "red"
          },
          {
            "shiftTypeId": "shiftType001",
            "name": "shift001",
            "color": "red"
          },
          {
            "shiftTypeId": "shiftType001",
            "name": "shift001",
            "color": "red"
          }
        ]
      }
    ]
  }
]

Thanks for your help in advance

vitticeps · Accepted Answer · 2019-07-23 16:38:50Z

This is doing exactly what I wanted;

db.mannings.aggregate([
    // where weekNumber = 30
    { "$match": {"weekNumber": 30}},

    // unwind locations
    { "$unwind": "$locations" },

    // unwind shifts - but keep it even if it is empty
    { "$unwind": { "path": "$locations.shifts", "preserveNullAndEmptyArrays": true } },

    // get the shiftType details from the other collection
    { "$lookup": {
            "let": { "userObjId": { "$toObjectId": "$locations.shifts.shiftTypeId" } },
            "from": "shiftTypes",
            "pipeline": [
                { "$match" : { "$expr": { "$eq" : [ "$_id", "$$userObjId"] } } }
            ],
            "as": "shiftType"
        }
    },

    // convert the first element of $lookup result (array) to object
    { "$addFields": {
            "shiftType": { "$arrayElemAt": [ "$shiftType", 0] }
        }
    },

    // add the name and color parameters to the locations.shifts object
    { "$addFields": {
            "locations.shifts.name": "$shiftType.name",
            "locations.shifts.color": "$shiftType.color"
        }
    },

    // 'remove' the $lookup result as we dont need it anymore
    { "$project": {
            "shiftType": 0
        }
    },

    // group up the unwinded locations
    { "$group": {
            "_id": "$_id",
            "locations": { "$addToSet": "$locations" }
        }
    }
]);

Edit:

A bit of change - with this grouping I get the location details in _id and the shifts in shifts

    { "$group": {
            "_id": {"_id": "$locations._id", "location": "$locations.location", "numberOfHeads": "$locations.numberOfHeads"},
            "shifts": { "$addToSet": "$locations.shifts" }
        }
    }

Collectives™ on Stack Overflow

MongoDB double nested array to reserve unwind

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related