Mongodb query by object key in array and replace the key

Question

How do I query for everyone who has a nickname and replace the nickname key with just "nick"

Dataset looks like this with a lot more fields and a lot more documents. the nickname obj is in an unknown index if it exists.

{
  _id: "robert",
  properties: [
    {
      "kids": 3
    },
    {
      "nickname": "Bob"
    },
    {
      "age": 45
    }
  ]
}

{
  _id: "alice",
  properties: [
    {
      "kids": 3
    },
    {
      "age": 45
    }
  ]
}

{
  _id: "joseph",
  properties: [
    {
      "nickname": "joe"
    }
  ]
}

In the end it should be:

{
  _id: "robert",
  properties: [
    {
      "kids": 3
    },
    {
      "nick": "Bob"
    },
    {
      "age": 45
    }
  ]
}

{
  _id: "alice",
  properties: [
    {
      "kids": 3
    },
    {
      "age": 45
    }
  ]
}

{
  _id: "joseph",
  properties: [
    {
      "nick": "joe"
    }
  ]
}

A tricky one! No single update statement can do that. [**$rename does not work if these fields are in array elements.**](https://docs.mongodb.com/manual/reference/operator/update/rename/). Iteration and update is best bet. [More info](https://stackoverflow.com/a/9127476/6082280) — ambianBeing, Sep 06 '19 at 21:01

score 1 · Accepted Answer · answered Sep 07 '19 at 04:30

The following query can do the trick. We are aggregating over collection to change the field properties.nickname to properties.nick and replacing existing data of collection with the aggregation output.

db.collection.aggregate([
  {
    $addFields:{
      "properties":{
          $map:{
          "input":"$properties",
          "as":"property",
          "in":{
            $mergeObjects:[
              "$$property",
              {
                "nick":"$$property.nickname"
              }
            ]
          }
        }
      }
    }
  },
  {
    $project:{
      "properties.nickname":0
    }
  },
  {
    $out:"collection"
  }
])

Before:

{
  "_id" : "robert",
  "properties" : [
    {
      "kids" : 3
    },
    {
      "nickname" : "Bob"
    },
    {
      "age" : 45
    }
  ]
}
{
  "_id" : "alice",
  "properties" : [
    {
      "kids" : 3
    },
    {
      "age" : 45
    }
  ]
}
{ "_id" : "joseph", "properties" : [ { "nickname" : "joe" } ] }

After:

{
  "_id" : "robert",
  "properties" : [
    {
      "kids" : 3
    },
    {
      "nick" : "Bob"
    },
    {
      "age" : 45
    }
  ]
}
{
  "_id" : "alice",
  "properties" : [
    {
      "kids" : 3
    },
    {
      "age" : 45
    }
  ]
}
{ "_id" : "joseph", "properties" : [ { "nick" : "joe" } ] }

What versions of mongodb does this work with? I don't have 4.2 yet. — Harry, Sep 07 '19 at 11:47

Mongodb query by object key in array and replace the key

1 Answers1