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.. Iteration and update is best bet. More info — ambianBeing
– ambianBeing, Commented Sep 6, 2019 at 21:01

Himanshu Sharma · Accepted Answer · 2019-09-07 04:30:49Z

1

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" } ] }

answered Sep 7, 2019 at 4:30

Himanshu Sharma

3,0102 gold badges10 silver badges19 bronze badges

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

2 Comments

Harry Over a year ago

What versions of mongodb does this work with? I don't have 4.2 yet.

Neeraj Sewani Over a year ago

Tried with MongoDB 4.4. It's wotking.

Collectives™ on Stack Overflow

Mongodb query by object key in array and replace the key

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related