Convert a CSV file with array columns to JSON file for MongoDB-CodePudding

i'm trying to import a csv file containing columns as arrays into a a Json file for MongoDB but when import the file with MongoDB it recognize the column containing the array as a String. This is what i have:

 _id: ObjectId("61f40cbf61f97f53a9aa4946"),
  name: 'Zipang',
  episodes: 26,
  premiered: 2004,
  genre: '[\'Action\', \'Military\', \'Sci-Fi\', \'Historical\', \'Drama\', \'Seinen\']' }

But i want it to be like this:

 _id: ObjectId("61f40cbf61f97f53a9aa4946"),
  name: 'Zipang',
  episodes: 26,
  premiered: 2004,
  genre: ["Action", "Military", "Sci-Fi", "Historical", "Drama", "Seinen"] }

CodePudding user response：

Try this one:

db.collection.updateMany({ genre: { $not: { $type: "array" } } }, [
   {
      $set: {
         genre: {
            $split: [{ $trim: { input: "$genre", chars: "[]" } }, ","]
         }
      }
   },
   {
      $set: {
         genre: {
            $map: { input: "$genre", in: { $trim: { input: "$$this", chars: " '" } } }
         }
      }
   },
])