JSON data modelling is a bit different from conventional data modelling, but the concepts remain the same. In course of my work as a data modeller, I have done JSON data modelling as well. In this article, I wanted to demonstrate some data modelling concepts which can be translated to JSON modelling and vice-versa.

This is not an elaborate JSON data modelling article. If you are an IT Coolie like me and want to quickly know how to do JSON data modelling and map it to conventional RDBMS data modelling using entities and attributes, this article provides a quick peek and tries to make you comfortable in JSON modelling. This is a cheat sheet for JSON data modelling.

Defining and Entity and Attribute

In the Below diagram you can see a simple entity A is declared which have 3 attributes. A1 is defined as PK.

A
Entity A with 3 varchar attributes

Following is a JSON representation of Entity ‘A’ in JSON.

{
	"$schema": "http://json-schema.org/draft-04/schema#",
	"type": "object",
	"title": "A.json",
	"description": "A test table in JSON Schema",
	"additionalProperties": false,
	"properties": {
		"A": {
			"A1": {
				"type": "string",
				"maxLength": 36,
				"primaryKey": true
			},
			"A2": {
				"type": "string",
				"maxLength": 36
			},
			"A3": {
				"type": "string",
				"maxLength": 36
			}
		},
		"required": [
			"A1"
		]
	}
}

You don’t have to declare the “maxLength” keyword if you have “type”: “integer”. Notice that I have marked the attribute ‘A1’ as the primary key. If you have candidate keys or not null columns, you can declare them under the “required” keyword. This is an array and can take multiple values, separated by comma (e.g. “required”: [“X1”, “X2”]. Since I have already declared A1 as the primary key, I did not need to add this keyword. This is for example purpose only.

I can declare other validations as well in the schema, such as “pattern”, “minLength” as well. For a full reference, please visit here.

One-to-Many Relationship

As per the below picture, I have 2 entities A and B and a one-to-many relationship. There are couple of ways to represent the same in JSON.

1M rel
1:M relationship in RDBMS

Nested Schema

Following is an example of representing entity B as an array nested in entity A. This is very important to understand. An array can hold many instances of the data. So, by declaring the entity B as an array, we are essentially defining that one instance of A can have multiple instances of B.

{
	"$schema": "http://json-schema.org/draft-04/schema#",
	"type": "object",
	"title": "ARel.json",
	"additionalProperties": false,
	"properties": {
		"A": {
			"A1": {
				"type": "string",
				"maxLength": 36,
				"primaryKey": true
			},
			"A2": {
				"type": "string",
				"maxLength": 36
			},
			"A3": {
				"type": "string",
				"maxLength": 36
			},
			"B": {
				"type": "array",
				"additionalItems": true,
				"uniqueItems": false,
				"items": {
					"B1": {
						"type": "string",
						"maxLength": 36,
						"primaryKey": true
					},
					"B2": {
						"type": "string",
						"maxLength": 36
					}
				}
			}
		},
		"required": [
			"A1"
		]
	}
}

Referencing Outside of the JSON Schema

This kind of declaration is a bit advanced form, but functionally the same. We can declare B in another JSON schema and call it in the ‘A’ schema. This is used where B has multiple relationships attached to it and you don’t want to write so many lines of same code (reusability). It is also used where the schema is volatile, and you want to change in one place and don’t want to touch multiple files (write-once-refer-many).

In this example, I create a JSON file to hold such common entities like below –

{
	"$schema": "http://json-schema.org/draft-04/schema#",
	"type": "object",
	"title": "CommonEntities.json",
	"description": " This JSON Schema holds reusable entities shared by multiple entities",
	"additionalProperties": false,
	"properties": {
		"B": {
		
			"B1": {
				"type": "string",
				"maxLength": 36,
				"primaryKey": true
			},
			"B2": {
				"type": "string",
				"maxLength": 36
			}
		},
		"required": [
			"B1"
		]
	}
}	

And I reference the entity B under the “properties” keyword like below –

{
	"$schema": "http://json-schema.org/draft-04/schema#",
	"type": "object",
	"title": "ARel.json",
	"additionalProperties": false,
	"properties": {
		"A": {
			"A1": {
				"type": "string",
				"maxLength": 36,
				"primaryKey": true
			},
			"A2": {
				"type": "string",
				"maxLength": 36
			},
			"A3": {
				"type": "string",
				"maxLength": 36
			},
			"BArray": {
				"type": "array",
				"additionalItems": true,
				"uniqueItems": false,
				"items": {
					"$ref": "CommonEntities.json#/properties/B"
				}
			},
			"required": [
				"A1"
			]
		}
	}
}

One-to-One Relationship

The one-to-one relationship says that entity A can have only one instance of entity B linked to it. So, we just change the “type” keyword from “array” to “object”. An “object” can only have one instance.

{
	"$schema": "http://json-schema.org/draft-04/schema#",
	"type": "object",
	"title": "ARel.json",
	"additionalProperties": false,
	"properties": {
		"A": {
			"A1": {
				"type": "string",
				"maxLength": 36,
				"primaryKey": true
			},
			"A2": {
				"type": "string",
				"maxLength": 36
			},
			"A3": {
				"type": "string",
				"maxLength": 36
			},
			"B": {
				"type": "object",
				"additionalItems": true,
				"uniqueItems": false,
				"items": {
					"B1": {
						"type": "string",
						"maxLength": 36,
						"primaryKey": true
					},
					"B2": {
						"type": "string",
						"maxLength": 36
					}
				}
			}
		},
		"required": [
			"A1"
		]
	}
}

The nesting and referencing technique described above, still holds good here.

Many-to-Many relationship

If you understand the 1:M and 1:1 relationship, you can pretty well guess the M:N. If you have time, I urge you to pause here and think about how it can be represented.

MN rel
M:N Relationship in RDBMS

As per the E-R diagram, A-B is a 1:M relationship. So, we have to declare an array of B under A. B-C is a M:1 relationship. So, B should have a nested object of C inside it. So, it should look something like below –

{
	"$schema": "http://json-schema.org/draft-04/schema#",
	"type": "object",
	"title": "ABCRel.json",
	"additionalProperties": false,
	"properties": {
		"A" : {
			"A1": {
				"type": "string",
				"maxLength": 36,
				"primaryKey": true
			},
			"A2": {
				"type": "string",
				"maxLength": 36
			},
			"A3": {
				"type": "string",
				"maxLength": 36
			},
			"B": {
				"type": "array",
				"additionalItems": true,
				"uniqueItems": false,
				"items": {
					"B1": {
						"type": "string",
						"maxLength": 36,
						"primaryKey": true
					},
					"B2": {
						"type": "string",
						"maxLength": 36
					}
				},
			"C": {
				"type": "object",
				"additionalItems": true,
				"uniqueItems": false,
				"items": {
					"C1": {
						"type": "string",
						"maxLength": 36,
						"primaryKey": true
					},
					"C2": {
						"type": "string",
						"maxLength": 36
					}
				}
			}
		},	
		"required": [
			"A1"
		]
		}
	}	
}

Tip 1: While you are modelling a table into JSON schema, try to follow the standard data modelling concepts and principles. Don’t let the flat structure of JSON deceive you to only create flat JSON schema. e.g. if you are modelling a financial transaction table, then you might have actual amount, tax amount, agent fees and bank charges, provided in different geographical location or currencies (e.g. UK Tax Amount, US Tax Amount, Dollar Amount, GBP amount etc.) In a standard modelling technique, you would create 1:M tables for Tax, Charge, Fee etc. You should also create arrays rather than include them all in one flat JSON structure.

Tip 2: Using JSON schema to define data model is tricky with all those commas and brackets. Use an online JSON validator like https://jsonlint.com/. All the above JSON schema are validated using JSONLint.

Please let me know in the comment section if this helped you to get a rudimentary understanding of JSON data modelling and whether you want some specific items to cover.

LEAVE A REPLY

Please enter your comment!
Please enter your name here