Tuesday 8 June 2021

MongoDB: How to add schema to the existing collection

You can apply a JSON schema to an existing collection using the collMod command.

 

Let’s create an employee collection by inserting some data into it.

> db.employee.insert({"empId" : 1, "name" : "Krishna"})
WriteResult({ "nInserted" : 1 })
>
>
> db.employee.insert({"empId" : 2, "name" : "Ram", "age" : 33})
WriteResult({ "nInserted" : 1 })
>
>
> 
> db.employee.find().pretty()
{
  "_id" : ObjectId("60c038918d62325981fe3ca9"),
  "empId" : 1,
  "name" : "Krishna"
}
{
  "_id" : ObjectId("60c038ba8d62325981fe3cab"),
  "empId" : 2,
  "name" : "Ram",
  "age" : 33
}

 

Attach schema to the existing collection

db.runCommand( {
   collMod: "employee",
   validator : {
      $jsonSchema : {
         bsonType: 'object',
         required: ['empId', 'firstName', 'lastName'],

         properties: {
            empId : {
               bsonType : 'int',
               description: 'empId must be an integer and mandatory property'
            },
            firstName : {
               bsonType: 'string',
               description: 'firstName must be a string and mandatory property'
            },
            lastName : {
               bsonType: 'string',
               description: 'lastName must be a string and mandatory property'
            },
            hobbies : {
               bsonType : 'array',
               description: 'Hobbies of user',
               items: {
                  bsonType: 'int'
               }
            },
            projects : {
               bsonType : 'array',
               description: 'Hobbies of user',
               required: ['pjtName', 'pjtId'],
               items: {
                  bsonType: 'object',
                  properties : {
                     pjtName : {
                        bsonType: 'string',
                        description: 'pjtName must be a string and mandatory property'
                     },
                     pjtId : {
                        bsonType: 'int',
                        description: 'pjtId must be a string and mandatory property'
                     }
                  }
               }
            }
         }

      }
   },
   validationLevel: "moderate"
} )

 

Let’s try to insert some invalid information

Employee with only empId and firstName.

> db.employee.insert({"empId": 123, "firstName": "Sailu"})
WriteResult({
	"nInserted" : 0,
	"writeError" : {
		"code" : 121,
		"errmsg" : "Document failed validation"
	}
})
>
>
> db.employee.insert({"empId": NumberInt(123), "firstName": "Sailu"})
WriteResult({
	"nInserted" : 0,
	"writeError" : {
		"code" : 121,
		"errmsg" : "Document failed validation"
	}
})

Employee with empId, firstName and lastName properties

> db.employee.insert({"empId": NumberInt(123), "firstName": "Sailu", "lastName" : "PTR"})
WriteResult({ "nInserted" : 1 })


Let’s try to add project property with some invalid information

> db.employee.insert({"empId": NumberInt(234), "firstName": "Keerthi", "lastName" : "Shetty", "projects": [{"pjtName": "CPCS", "pjtId": 123}]})
WriteResult({
	"nInserted" : 0,
	"writeError" : {
		"code" : 121,
		"errmsg" : "Document failed validation"
	}
})

  In the above example the property "pjtId" should be of type NumberInt. Let’s correct and insert the data.

 

> db.employee.insert({"empId": NumberInt(234), "firstName": "Keerthi", "lastName" : "Shetty", "projects": [{"pjtName": "CPCS", "pjtId": NumberInt(123)}]})
WriteResult({ "nInserted" : 1 })

 

Let’s print all the documents of employee collection.

> db.employee.find().pretty()
{
	"_id" : ObjectId("60c038918d62325981fe3ca9"),
	"empId" : 1,
	"name" : "Krishna"
}
{
	"_id" : ObjectId("60c038ba8d62325981fe3cab"),
	"empId" : 2,
	"name" : "Ram",
	"age" : 33
}
{
	"_id" : ObjectId("60c03a328d62325981fe3cae"),
	"empId" : 123,
	"firstName" : "Sailu",
	"lastName" : "PTR"
}
{
	"_id" : ObjectId("60c03a7a8d62325981fe3cb1"),
	"empId" : 234,
	"firstName" : "Keerthi",
	"lastName" : "Shetty",
	"projects" : [
		{
			"pjtName" : "CPCS",
			"pjtId" : 123
		}
	]
}

 

Note

Schema validation will only apply to new write operations it will not run against existing documents in the collection.

 

 

Previous                                                    Next                                                    Home

No comments:

Post a Comment