Today, I was doing solution design for a system when I started to think when we should use JSON data type for columns. Coming up with the right schema design takes multiple iterations. I consider it more as an art than science. All the mainstream RDBMS have JSON data type support.
- Postgres has JSON data type since version 9.2. The 9.2 version was released in September 2012
- MySQL has JSON data type since version 5.7.8. The 5.7.8 version was released in August 2015
- SQL Server has JSON data type since version SQL Server 2016. The 2016 version was released in June 2016
- Oracle has JSON data type since version 19c. The 19c version was released in February 2019
They all support efficient insertion and querying of JSON data type. I will not compare their JSON capabilities today. Today, I want to answer a design question – when should a column have a JSON data type?
I use the JSON data type in design situations mentioned below. There could be other places as well where JSON is a suitable data type.
- Dump request data that will be processed later
- Support extra fields
- One To Many Relationship where many side will not have to its own identity
- Key Value use case
- Simpler EAV design
Let’s talk about each of these use cases in more detail.Continue reading “When to use JSON data type in database schema design?”