Mongo Aggregation to replace an empty / null array field inside an array with a default array

Question

I have a collection like below

{
    relatedProperties: [ //Array
       {
            locations: [ //Array 
                {
                    value: "Brazil"
                },
                {
                    value: "Germany"
                }
            ]
       },
       {
            locations: []
       },
       {
            locations: null
       }
    ]
}

How do I write an aggregation to make only the empty or null arrays to have a default value like;

locations: [  
                {
                    value: "India"
                }
           ]

Alex Blex · Accepted Answer · 2019-08-28 11:20:44Z

You can use $mergeObjects to keep other fields whatever they are:

db.collection.aggregate([
  {
    $project: {
      relatedProperties: {
        $map: {
          input: "$relatedProperties",
          as: "rp",
          in: {
            $cond: {
              if: {
                $eq: [
                  {
                    $ifNull: [
                      "$$rp.locations",
                      []
                    ]
                  },
                  []
                ]
              },
              then: {
                $mergeObjects: [
                  "$$rp",
                  {
                    locations: [
                      {
                        value: "India"
                      }
                    ]
                  }
                ]
              },
              else: "$$rp"
            }
          }
        }
      }
    }
  }
])

Himanshu Sharma · Accepted Answer · 2019-08-28 11:16:25Z

1

The processing can also be done using $map operator. The following query can get us the expected output:

db.collection.aggregate([
  {
    $addFields:{
      "relatedProperties":{
        $map:{
          "input":"$relatedProperties",
          "as":"relatedProperty",
          "in":{
            "name":"$$relatedProperty.name",
            "age":"$$relatedProperty.age",
            "org":"$$relatedProperty.org",
            "locations":{
              $cond:[
                {
                  $in:["$$relatedProperty.locations",[null,[]]]
                },
                [
                  {
                    "value":"India"
                  }
                ],
                "$$relatedProperty.locations"
              ]
            }
          }
        }
      }
    }
  }
]).pretty()

Data set:

{
    "_id" : ObjectId("5d666236986fb04b2aeabe2a"),
    "relatedProperties" : [
        {
            "locations" : [
                {
                    "value" : "Brazil"
                },
                {
                    "value" : "Germany"
                }
            ],
            "name" : "ABC",
            "age" : "12",
            "org" : {
                "value" : "org1"
            }
        },
        {
            "locations" : [ ],
            "name" : "CDE",
            "age" : "30",
            "org" : {
                "value" : "org2"
            }
        },
        {
            "locations" : null,
            "name" : "EFG",
            "age" : "20",
            "org" : {
                "value" : "org3"
            }
        }
    ]
}

Output:

{
    "_id" : ObjectId("5d666236986fb04b2aeabe2a"),
    "relatedProperties" : [
        {
            "name" : "ABC",
            "age" : "12",
            "org" : {
                "value" : "org1"
            },
            "locations" : [
                {
                    "value" : "Brazil"
                },
                {
                    "value" : "Germany"
                }
            ]
        },
        {
            "name" : "CDE",
            "age" : "30",
            "org" : {
                "value" : "org2"
            },
            "locations" : [
                {
                    "value" : "India"
                }
            ]
        },
        {
            "name" : "EFG",
            "age" : "20",
            "org" : {
                "value" : "org3"
            },
            "locations" : [
                {
                    "value" : "India"
                }
            ]
        }
    ]
}

edited Aug 28, 2019 at 11:16

answered Aug 28, 2019 at 10:22

Himanshu Sharma

3,0102 gold badges9 silver badges19 bronze badges

7 Comments

Boat Over a year ago

Hey Sharma, thanks for the help... but I do not want to perform an 'unwind' operation for a reason. Looking for the "$addFields" operation which can just replace all empty / null arrays with default.

Himanshu Sharma Over a year ago

@Shibu Please try the second approach mentioned in the answer.

Boat Over a year ago

@Sharma, second approach worked fine... but other fields adjacent to locations are omitted in the result.

Himanshu Sharma Over a year ago

Can you please post complete sample document?

Boat Over a year ago

{ "relatedProperties": [{ "locations": [{ "value": "Brazil" }, { "value": "Germany" }], "name": "ABC", "age": "12", "org": { "value": "org1" } }, { "locations": [], "name": "CDE", "age": "30", "org": { "value": "org2" } }, { "locations": null, "name": "EFG", "age": "20", "org": { "value": "org3" } }] }

|

Collectives™ on Stack Overflow

Mongo Aggregation to replace an empty / null array field inside an array with a default array

2 Answers 2

Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related