Mongodb group and push with empty arrays

Question

I'm having a problem with a group when there is an array that could be empty. The collection could be like this:

{
    "_id" : "Contract_1",
    "ContactId" : "Contact_1",
    "Specifications" : [ ]
}

{
    "_id" : "Contract_2",
    "ContactId" : "Contact_2",
    "Specifications" : [ 
        {
            "Description" : "Descrizione1",
            "VehicleId" : "Vehicle_1",
            "Customizations" : [ 
                {
                    "Description" : "Random furniture",
                    "ContactId" : "Contact_5"
                }, 
                {
                    "Description" : "Random furniture 2",
                    "ContactId" : "Contact_3"
                }
            ]
        }, 
        {
            "Description" : "Descrizione2",
            "VehicleId" : "Vehicle_2",
            "Customizations" : [ 
                {
                    "Description" : "Random furniture",
                    "ContactId" : "Contact_5"
                }, 
                {
                    "Description" : "Random furniture 2",
                    "ContactId" : "Contact_3"
                }
            ]
        }
    ]
}

{
    "_id" : "Contract_3",
    "ContactId" : "Contact_25",
    "Specifications" : [ 
        {
            "Description" : "Descrizione1",
            "VehicleId" : "Vehicle_1",
            "Customizations" : []
        }, 
        {
            "Description" : "Descrizione2",
            "VehicleId" : "Vehicle_2",
            "Customizations" : []
        }
    ]
}

As you can see, sometimes Specifications can be null, and also Customizations. And this is the query that I execute:

db.getCollection("Contract").aggregate([
  { "$lookup": {
    "from": "Contact",
    "localField": "ContactId",
    "foreignField": "_id",
    "as": "Contact"
  }},
  { "$unwind": {"path":"$Contact", "preserveNullAndEmptyArrays":true }},
  { "$unwind": { "path": "$Specifications", "preserveNullAndEmptyArrays":true }},
  { "$lookup": {
    "from": "Vehicle",
    "localField": "Specifications.VehicleId",
    "foreignField": "_id",
    "as": "Specifications.Vehicle"
  }},
  { "$unwind": {"path": {"$Specifications.Vehicle","preserveNullAndEmptyArrays":true} },
  { "$unwind": {"path": {"$Specifications.Customizations","preserveNullAndEmptyArrays":true} },
  { "$lookup": {
    "from": "Contact",
    "localField": "Specifications.Customizations.ContactId",
    "foreignField": "_id",
    "as": "Specifications.Customizations.Contact"
  }},
  { "$unwind": {"path": {"$Specifications.Customizations.Contact","preserveNullAndEmptyArrays":true} },
  { "$group": {
    "_id": {
      "_id": "$_id",
      "Description": "$Specifications.Description"
    },
    "ContactId": { "$first": "$ContactId" },
    "Contact": { "$first": "$Contact" },
    "Specifications": {
      "$push": "$Specifications.Customizations"
    }
  }},
  { "$group": {
    "_id": "$_id._id",
    "ContactId": { "$first": "$ContactId" },
    "Contact": { "$first": "$Contact" },
    "Specifications": {
      "$push": {
        "Description": "$_id.Description",
        "Customizations": "$Specifications"
      }
    }
  }}
])
}},
   { "$group": {
    "_id": "$_id._id",
    "ContactId": { "$first": "$ContactId" },
    "Contact": { "$first": "$Contact" },
    "Specifications": {
      "$push": {
        "Description": "$_id.Description",
        "Customizations": "$Specifications"
      }
    }
  }}
])

Once the query execute, when it's doing the 2 $group it creates a problem, since for the first one when pushing $Specifications.Customizations will create an array with an empty element inside. What I want is that If Specifications is an empty array, will stay so without adding an empty element inside.

but I want to keep the document, if I set to false when unwinding specifications for example, I lose the document Edit: Sorry I put the wrong query — Davide Quaglio
– Davide Quaglio, Commented May 26, 2019 at 10:35
@Fanpark yes, now I edited with the correct query that I'm using. It's true for every unwind. But when grouping, if I have Specifications as empty array, as output I still want it as empty array, but I get an array with an empty element inside — Davide Quaglio
– Davide Quaglio, Commented May 26, 2019 at 10:43
I think the problem is generated in the lookup, but should be solved in the group, with a filter maybe — Davide Quaglio
– Davide Quaglio, Commented May 26, 2019 at 11:19

Ashh · Accepted Answer · 2019-05-26 11:40:13Z

1

This is I can see one of the drawback of the $unwind and $group for the nested arrays. To get rid from this you need to add one more stage $addFields to filter out the empty nested arrays.

Add this at the end of the pipeline

{ "$addFields": {
  "Specifications": {
    "$filter": {
      "input": "$Specifications",
      "cond": { "$ne": ["$$this.Description", undefined] }
    }
  }
}}

answered May 26, 2019 at 11:40

Ashh

46.6k16 gold badges111 silver badges137 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Maximilian Schaum · Accepted Answer · 2021-05-26 13:51:48Z

1

For anyone still having the same problem like me and for whom the answer from @Ashh isnt working (I cant figure out the reason why it doesnt work for me). $ifNull instead of $ne is what worked for me, like this:

{ "$addFields": {
  "Specifications": {
    "$filter": {
      "input": "$Specifications",
      "cond": { "$ifNull": ["$$this.Description", false] }
    }
  }
}}

answered May 26, 2021 at 13:51

Maximilian Schaum

2703 silver badges9 bronze badges

Collectives™ on Stack Overflow

Mongodb group and push with empty arrays

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related