How can I change datatype of a field from string to array in a nested array of documents

Working with Data Developer Tools
,
1

I want to convert the datatype of field reviewerEmail string to array, below is the data I am working on

[
  {
    _id: ObjectId("632c12d98b4d9347774a4e9f"),
    "dimensions": {
      "width": 23.17,
      "height": 14.43,
      "depth": 28.01
    },
    "reviews": [
      {
        "rating": 2,
        "comment": "Very unhappy with my purchase!",
        "date": "2024-05-23T08:56:21.618Z",
        "reviewerName": "John Doe",
        "languages": "English",
        "reviewerDetails": {
          "reviewerEmail": "john.doe@x.dummyjson.com",
          "reviewerAge": 37
        }
      },
      {
        "rating": 3,
        "comment": "Not as described!",
        "date": "2024-05-23T08:56:21.618Z",
        "reviewerName": "Nolan Gonzalez",
        "languages": "Chinese",
        "reviewerDetails": {
          "reviewerEmail": [
            "nolan.gonzalez@x.dummyjson.com"
          ],
          "reviewerAge": 35
        }
      },
      {
        "rating": 5,
        "comment": "Very satisfied!",
        "date": "2024-05-23T08:56:21.618Z",
        "reviewerName": "Scarlett Wright",
        "reviewerDetails": {
          "reviewerEmail": "scarlett.wright@x.dummyjson.com",
          "reviewerAge": 30
        }
      }
    ]
  }
]

I have applied the below query

db.collection.update({
  "$and": [
    {
      "reviews.reviewerDetails.reviewerEmail": {
        "$exists": true
      }
    },
    {
      "reviews.reviewerDetails.reviewerEmail": {
        "$type": "string"
      }
    }
  ]
},
[
  {
    "$set": {
      "reviews.reviewerDetails.reviewerEmail": "$reviews.reviewerDetails.reviewerEmail"
    }
  }
])

But It returns the wrong output as mentioned the below result

[
  {
    "_id": ObjectId("632c12d98b4d9347774a4e9f"),
    "dimensions": {
      "depth": 28.01,
      "height": 14.43,
      "width": 23.17
    },
    "reviews": [
      {
        "comment": "Very unhappy with my purchase!",
        "date": "2024-05-23T08:56:21.618Z",
        "languages": "English",
        "rating": 2,
        "reviewerDetails": {
          "reviewerAge": 37,
          "reviewerEmail": [
            "john.doe@x.dummyjson.com",
            [
              "nolan.gonzalez@x.dummyjson.com"
            ],
            "scarlett.wright@x.dummyjson.com"
          ]
        },
        "reviewerName": "John Doe"
      },
      {
        "comment": "Not as described!",
        "date": "2024-05-23T08:56:21.618Z",
        "languages": "Chinese",
        "rating": 3,
        "reviewerDetails": {
          "reviewerAge": 35,
          "reviewerEmail": [
            "john.doe@x.dummyjson.com",
            [
              "nolan.gonzalez@x.dummyjson.com"
            ],
            "scarlett.wright@x.dummyjson.com"
          ]
        },
        "reviewerName": "Nolan Gonzalez"
      },
      {
        "comment": "Very satisfied!",
        "date": "2024-05-23T08:56:21.618Z",
        "rating": 5,
        "reviewerDetails": {
          "reviewerAge": 30,
          "reviewerEmail": [
            "john.doe@x.dummyjson.com",
            [
              "nolan.gonzalez@x.dummyjson.com"
            ],
            "scarlett.wright@x.dummyjson.com"
          ]
        },
        "reviewerName": "Scarlett Wright"
      }
    ]
  }
]

Anyone from the team can help me on this issue.

1 Like
2

I was having a play and got a basic something working but not complete as tied up.

The idea is to use the $map function and $mergeObject so you can iterate over very element of the array of items, for each one you the use a $cond operator to either pull just the data or the first array element of the data, resulting in them all being a string as opposed to array.

db.getCollection("Demo4").insertOne({
    stuff:[
        {'name':'1', 'data':'stuff'},
        {'name':'2', 'data':['stuff']},
    ]
})

db.getCollection("Demo4").aggregate([
{
    $addFields:{
        newStuff:{
            $map:{
                input:'$stuff',
                as:'myData',
                in:{
                    $mergeObjects:[
                        '$$myData',
                        {
                            'data':{
                                $cond:{
                                    if:{
                                        $eq:[
                                            'array',
                                            {$type:'$$myData.data'}
                                        ]
                                    },
                                    then:{$arrayElemAt:['$$myData.data', 0]},
                                    else:'$$myData.data'
                                }
                            }
                        }
                    ]
                }
            }
        }
    }
}
])

In the example above I’m using an aggregation and $addFields to just show the output, as well as the original. In an update you can just perform an update with aggregation format update statement, i.e. enclose the update part in array markers:

db.collection.updateMany({}, [{$set:{'A':'B'}}])
1 Like