MongoDB convert a JSON-String to actual Objects of array "[{obj1},{obj2}]"-CodePudding

I have imported a dataset which contains several documents and each document contain a genres attribure, Its value is string - A json string like "[{'field1':'value1'}, {'field2':'value2'}]", please notice the quote I want this to be array of objects

{
  "genres": "[{'id': 16, 'name': 'Animation'}, {'id': 35, 'name': 'Comedy'}, {'id': 10751,'name': 'Family'}]"
}

Above string values to be converted in following way

{
    "title": "Toy story collection",
    "genres": [
        {'id': 16, 'name': 'Animation'}, 
        {'id': 35, 'name': 'Comedy'}, 
        {'id': 10751,'name': 'Family'}
    ]
},
{
    "title": "Shrek",
    "genres": [
        {'id': 16, 'name': 'Animation'} 
    ]
},
{
    "title": "Full House",
    "genres": [
        {'id': 10751,'name': 'Family'}
    ]
}

This will enable me to search records as follows

db.moviesCollection.find({"genres.name":"Animation"})

A solution i can think of is load each document one by one though php or javascript and start converting it.

Is there a way? I can update documentes directly through mongodb itself without using any programming lanaguge.

Another answer mention something like following but did not mention if they are using any language.

db.movies_metadata.find({}).snapshot().forEach(function (el){el.genres=JSON.parse(el.genres);db.movies_metadata.save(el)});

If I run above directly on console, I see following error.

uncaught exception: TypeError: db.movies_metadata.find(...).snapshot is not a function : @(shell):1:1

CodePudding user response：

First of all the value you provided is not a valid JSON, it will be something like this:

{
    "genres": "[{\"id\":16,\"name\":\"Animation\"},{\"id\":35,\"name\":\"Comedy\"},{\"id\":10751,\"name\":\"Family\"}]"
}

Now, there is a way to parse this JSON, within MongoDB itself, using the $function stage, and write to a collection using $merge, provided you have mongodb version 4.4 or above, like this:

db.collection.aggregate([
  {
    "$project": {
      "genres": {
        "$function": {
          "body": "function(genres) { return JSON.parse(genres) }",
          "args": [
            "$genres"
          ],
          "lang": "js"
        }
      }
    }
  },
  {
    "$merge": {
      "into": "collectionName",
      "on": "_id",
      "whenMatched": "replace",
      "whenNotMatched": "insert"
    }
  }
])

See, it working here.

This query will work with your existing data:

db.collection.aggregate([
  {
    "$project": {
      "genres": {
        "$function": {
          "body": "function(genres) { genres = genres.replace(/'/g, '\"'); return JSON.parse(genres) }",
          "args": [
            "$genres"
          ],
          "lang": "js"
        }
      }
    }
  },
  {
    "$merge": {
      "into": "collectionName",
      "on": "_id",
      "whenMatched": "replace",
      "whenNotMatched": "insert"
    }
  }
])

Playground link.