I currently have a structured database like

return new Schema({
_id: {
    type: mongoose.Schema.Types.ObjectId,
    auto: true
},
name: {type: String, unique: false, required: true},
criteria: [
    {
        _id: {
            type: mongoose.Schema.Types.ObjectId,
            auto: true
        },
        category: {type: String, required: false},
        links: {type: [String], required: false, default: []},
        dateOfEntry: {
            type: Date,
            default: new Date(),
            required: true
        },
        lastUpdated: {
            type: Date,
            default: new Date(),
            required: true
        }
    }
],
isActive: {type: Boolean, required: false, default: true}});

Sample data

[
  {
    "name": "item1",
    "criteria": [
      {
        "category": "category_A",
        "links": [
          "link_issue1",
          "link_issue2",
          "link_issue3"
        ]
      },
      {
        "category": "category_B",
        "links": [
          "link_issue1",
          "link_issue2"
        ]
      },
      
    ]
  },
  {
    "name": "item2",
    "criteria": [
      {
        "category": "category_C",
        "links": []
      },
    ]
  }
]

So now, I want to change the data type of links from array to string and update the existing data as well

My expected like

[
  {
    "name": "item1",
    "criteria": [
      {
        "category": "category_A",
        "links": "link_issue1,link_issue2,link_issue3"
      },
      {
        "category": "category_B",
        "links": "link_issue1,link_issue2"
      },
      
    ]
  },
  {
    "name": "item2",
    "criteria": [
      {
        "category": "category_C",
        "links": ""
      },
      
    ]
  }
]

My database is having about 500-1000 records that need updating. So can we update them by MongoDB shell? Thanks a lot for your help.

CodePudding user response：

use $concat

db.collection.update({},
[
  {
    $set: {
      criteria: {
        $map: {
          input: "$criteria",
          as: "c",
          in: {
            category: "$$c.category",
            links: {
              "$reduce": {
                "input": "$$c.links",
                "initialValue": "",
                "in": {
                  "$concat": [
                    "$$value",
                    "$$this",
                    {
                      $cond: {
                        if: {
                          "$eq": [
                            { $subtract: [ { $size: "$$c.links" }, 1 ] },
                            { $indexOfArray: [ "$$c.links", "$$this" ] }
                          ]
                        },
                        then: "",
                        else: ","
                      }
                    }
                  ]
                }
              }
            }
          }
        }
      }
    }
  }
],
{
  "multi": true
})

mongoplayground