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

Question

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

Updated Answer: After getting a great answer here is my final update query, I am using merge instead of replace as I want to preserve the rest of the attributes

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

the problem is how to convert the string that contains the json, to real json object? I am not sure that i understand, if this is what you need maybe you need JSON.parse(your_json_string) if you use javascript — Takis
– Takis, Commented Aug 6, 2022 at 15:16

Charchit Kapoor · Accepted Answer · 2022-08-07 06:02:02Z

1

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.

edited Aug 7, 2022 at 6:02

answered Aug 7, 2022 at 5:29

Charchit Kapoor

9,3242 gold badges11 silver badges26 bronze badges

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

3 Comments

PHP Avenger Over a year ago

I am afraid thats how data is availble, instead of backslash with double quote, its available as single quote. can we do something in between to replace single quote with that? (some chain, mapping)

PHP Avenger Over a year ago

It works as per expectations but it is also removing rest of the attributes

Charchit Kapoor Over a year ago

Use $addFields instead of $project

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related