When to use JSON data type in database schema design?

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. 

  1. Dump request data that will be processed later
  2. Support extra fields
  3. One To Many Relationship where many side will not have to its own identity
  4. Key Value use case
  5. 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?”