“blue and brown bird on brown tree trunk” by Boris Smokrovic on Unsplash

Using JSON Datatype In Relational Database To Develop Flexible/Configurable Software

Relational Database Systems like MySQL, PostgreSQL, MariaDB and others started supporting storing and indexing JSON data natively. This article is to explain how we can use JSON datatype to develop flexible or configurable software product or application.

Idea behind using JSON columns in Relational Database is to leverage some of the flexibility or benefits of schema-less design similar to NoSQL databases which supports schema-less design.

Imagine, we are working on developing Patient Portal. Requirement is to create Patient Profile with following required attributes. Also, provide ability for Clinics to add custom attributes or fields and values to Patient Profile.

Following are the attributes of Patient Profile:

1. First Name
2. Last Name
3. Date of Birth
4. Custom Attributes* (other attributes)

Examples and code snippets in this article will be using MySQL 5.7, Java, Spring Data JPA.

Traditional Solution

We used to create columns like attribute_1, attribute_2, attribute_n…

Note: To understand the concept, assume one of the clinic wants to configure couple of custom attributes ‘Height’ and ‘Weight’ as below,

Figure 1: attribute_meta_data table having meta-data for attribute_1 and attribute_2 columns of patient_profile table.

Figure 2: patient_profile table having values for custom attributes (attribute 1 -> height, attribute 2 -> weight)

Solution Using JSON Datatype Columns

This use case can be done better using JSON datatype support in Relational Database Systems. Hence patient_profile table can be created as,

Figure 3: patient_profile table having values for custom or other attributes as JSON

Now the question is, how do we convert this to a Java Object when using JPA?

Java Representation for the above table will be,

Following attribute converter does serialization and deserialization of Map<String, String> to JSON and vice versa.

Other Use Cases where JSON Datatype Columns can be used

List<String> to JSON Array

Set<String> to JSON Array

If Map, List, Set is not good enough, you can even use a Java Object like

List<Contact> to JSON Object

Things To Be Careful Of

Relational Databases are build on the base of Normalization. Hence don’t try to use JSON column to replace all One To Many relationships.

JSON datatype columns use LONGTEXT /LONGBLOB or some form of CLOB/BLOB. Before using JSON datatype columns read the respective database documentation to understand and do performance testing.

Limit the size of JSON data in these columns where ever possible.

Conclusion

I believe, performance and flexibility in software are inversely proportional. Finding a middle ground and using JSON datatype with knowledge of its pros and cons will help build better Software Product and Happy Users.

References:

MySQL :: MySQL 5.7 Reference Manual :: 11.6 The JSON Data Type

PostgreSQL: Documentation: 10: 8.14. JSON Types

JSON Data Type