Updating nested emdedded list containing BasicDBObject to DBRef in mongoDB using groovy

Question

The structure of document in mongo is as follows

Project(@Entity)
  |--Deliverables(@Embedded, list inside Project)
       |--DeliveryTypes(@Embedded, list inside Deliverables)
            |--DeliveryItems(Plain list inside DeliveryTypes)
                 |--log(Plain list inside DeliveryItems)

and the other possible structure of document in mongo is as follows

Project(@Entity)
  |--Deliverables(@Embedded, list inside Project)
       |--DeliveryTypes(@Embedded, list inside Deliverables)
            |--DeliveryItems(Plain list inside DeliveryTypes)
                 |--Tasks(Plain list inside DeliveryItems)
                      |--log(Plain list inside Tasks)
                 |--log(Plain list inside DeliveryItems)

This is how the Project document looks like in mongoDB

{
"_id" : ObjectId("51827f4fe4b07cc5088149ff"),
"className" : "Project",
"name" : "TestProject",
"description" : "This is a test project",
"state" : "Open",
"dateCreated" : ISODate("2013-05-02T14:59:27.069Z"),
"projectStatuses" : [
    {
        "status" : "On Track",
        "dateCreated" : ISODate("2013-05-02T14:59:27.071Z"),
        "user" : {
                   "_id" : "[email protected]",
                   "firstName" : "Test",
                   "lastName" : "User",
                   "enabled" : true,
                   "accountExpired" : false,
                   "accountLocked" : false,
                   "passwordExpired" : false
                 }
    }
],
"commercialStatuses" : [
    {
        "status" : "On Track",
        "dateCreated" : ISODate("2013-05-02T14:59:27.074Z"),
        "user" : {
                   "_id" : "[email protected]",
                   "firstName" : "Test",
                   "lastName" : "User",
                   "enabled" : true,
                   "accountExpired" : false,
                   "accountLocked" : false,
                   "passwordExpired" : false
                 }
    }
],
"deliverables" : [
    {
        "embeddedId" : ObjectId("5183702fe4b014bfbe387d37"),
        "name" : "TestSite 01",
        "deliveryTypes" : [
            {
                "deliveryItems" : [
                    {
                        "embeddedId" : ObjectId("5183702fe4b014bf00000003"),
                        "type" : "Plain",
                        "log" : [
                            {
                                "dateCreated" : ISODate("2013-05-03T08:42:10.592Z"),
                                "oldValue" : "Open",
                                "newValue" : "Closed",
                                "user" : {
                                           "_id" : "[email protected]",
                                           "firstName" : "Test",
                                           "lastName" : "User",
                                           "enabled" : true,
                                           "accountExpired" : false,
                                           "accountLocked" : false,
                                           "passwordExpired" : false
                                          }
                            },
                            {
                                "dateCreated" : ISODate("2013-05-03T09:24:30.336Z"),
                                "oldValue" : "Closed",
                                "newValue" : "Open",
                                "user" : {
                                           "_id" : "[email protected]",
                                           "firstName" : "Test",
                                           "lastName" : "User",
                                           "enabled" : true,
                                           "accountExpired" : false,
                                           "accountLocked" : false,
                                           "passwordExpired" : false
                                         }
                            },
                            {
                                "dateCreated" : ISODate("2013-05-03T13:33:06.550Z"),
                                "oldValue" : "Open",
                                "newValue" : "Closed",
                                "user" : {
                                           "_id" : "[email protected]",
                                           "firstName" : "Test",
                                           "lastName" : "User",
                                           "enabled" : true,
                                           "accountExpired" : false,
                                           "accountLocked" : false,
                                           "passwordExpired" : false
                                         }
                            }
                        ]
                    },
                    {
                        "embeddedId" : ObjectId("5183702fe4b014bf00000004"),
                        "type" : "task",
                        "tasks" : [
                            {
                                "embeddedId" : ObjectId("518370abe4b014bf00000001"),
                                "name" : "TestSubTask 1",
                                "log" : [
                                    {
                                        "dateCreated" : ISODate("2013-05-03T08:09:15.624Z"),
                                        "oldValue" : "Open",
                                        "newValue" : "Created",
                                        "user" : {
                                                  "_id" : "[email protected]",
                                                  "firstName" : "Test",
                                                  "lastName" : "User",
                                                  "enabled" : true,
                                                  "accountExpired" : false,
                                                  "accountLocked" : false,
                                                  "passwordExpired" : false
                                                }
                                    }
                                ],
                                "plannedEndDate" : ISODate("2013-05-02T22:00:00Z"),
                                "assignedUser" : "Test person",
                                "description" : "This is a test sub task"
                            }
                        ],
                        "log" : [
                            {
                                "dateCreated" : ISODate("2013-05-03T08:07:52.725Z"),
                                "oldValue" : "Open",
                                "newValue" : "Closed",
                                "user" : {
                                           "_id" : "[email protected]",
                                           "firstName" : "Test",
                                           "lastName" : "User",
                                           "enabled" : true,
                                           "accountExpired" : false,
                                           "accountLocked" : false,
                                           "passwordExpired" : false
                                         }
                            }
                        ]
                    }
                ]
            }
   ]
}

now the problem I have, I want to change all the occurrences of

"user" : {
          "_id" : "[email protected]",
          "firstName" : "Test",
          "lastName" : "User",
          "enabled" : true,
          "accountExpired" : false,"accountLocked" : false,
          "passwordExpired" : false
         }

under

projectStatuses, commercialStatuses, and log 

to

"user" : DBRef("User", "[email protected]")

This is what I have tried so far,

Mongo connection = new Mongo("localhost", 27017)
DB db = connection.getDB('test')

DBCollection projectCollection = Project.collection
QueryBuilder projectQuery = new QueryBuilder()
BasicDBObject projectKeys = new BasicDBObject()
DBCursor projectCursor = projectCollection.find(projectQuery.get(), projectKeys)
ArrayList projects = projectCursor.toArray()

projects.each { project ->
   project.deliverables.each { deliverable ->
      deliverable.deliveryTypes.each { deliveryType ->
         deliveryType.deliveryItems.each { deliveryItem ->
            deliveryItem.log.each { log ->
               updateLogUser(log, db)
            }
         }
      }
   }
}

static updateLogUser(def log, DB db) {
    if (log.user?._id) {
        log.user = new DBRef(db, "User", log.user?._id)
    }
}

The above code does what I need, but the problem now is how do I save the updated, queried objects? I tried the following but grails keep throwing exception "cannnot cast BasicDBObject to DBRef" when I try to login :/

projects.each { project ->
        Project.update(['_id': project._id]) {set "deliverables", project.deliverables}
    }

I have checked the db after migration and there is no instance of user object, there are only references, but still get the exception. I am not really good with writing migrating scripts, so if someone can help me in how to save queried documents (which looks like a big map), it would be great, or some useful links to how-to documents would also be helpful. Thanks in advance :)

Answer 1

okay I have finally managed to update embedded list containing mongo's BasicBDObject to mongo DBRef Object.

first you should establish a connection with the following code. The below code connects to "test" database at port "27017" (default port) on your local machine.

Mongo connection = new Mongo("localhost", 27017)
DB db = connection.getDB('test')

next write a query which gets all the documents stored in mongo, that needs to be updated, in my case all of the documents. The following code helps you do that.

//specify which collection you want to query 
DBCollection projectCollection = Project.collection
//specify the query using the following code, in my case all 
//documents so I leave the query object empty
QueryBuilder projectQuery = new QueryBuilder()
//specify what fields of the document should be retrieved, in my case
//I need all the fields, so I leave it empty
BasicDBObject projectKeys = new BasicDBObject()
//initialize a cursor, it is more like an iterator, to the results
DBCursor projectCursor = projectCollection.find(projectQuery.get(), projectKeys)
//convert the obtained results to an ArrayList
ArrayList projects = projectCursor.toArray()

iterate through the results and update what you want to update

projects.each { project ->
project.deliverables.each { deliverable ->
  deliverable.deliveryTypes.each { deliveryType ->
     deliveryType.deliveryItems.each { deliveryItem ->
        deliveryItem.log.each { log ->
           updateLogUser(log.user, db)
        }
      }
    }
  }
}

static updateLogUser(def user, DB db) {
  try {
        //user.fetch is to check whether the object is BasicDBObject
        //or DBRef object. if user object is a BasicDBObject then .fetch()
        //throws an exception else it gets the referenced object
        if (user && user.fetch()) {
            return user
        } else {
            return null
        }
    } catch (Exception e) {
        return new DBRef(db, "User", user._id)
    }
}

and now to save the changes you have made to the document.

projects.each { project ->
   !project.deliverables ?: 
      Project.update(['_id': project.id]) {set "deliverables", project.deliverables}
}

Since you cannot directly update the nested list in mongodb, which is bug in mongo, and is reported to mongo, hopefully they fix it soon, you can update the parent of nested lists, in my case it is the "deliverables" list which is the parent of nested lists