json – Shekhar Gulati

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.

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.

TIL #3: Exclude null fields in Spring Boot REST JSON API, Serializing Enum value with Jackson, and Change remote for a branch in Git

The three things that I learned today are mentioned below.

Learning 1: Exclude null fields in Spring Boot REST JSON API response

Spring Boot uses Jackson to convert to JSON. Spring Boot allows you to configure through a configuration property whether you want to include null values or not. By default, serialised JSON will include null values as well. To remove null values, you should use following property add it to your application.properties.

spring.jackson.default-property-inclusion=NON_NULL

If you are using Java 8 or Google’s Gauva and want to exclude Optional type as well then you should use NON_ABSENT value.

spring.jackson.default-property-inclusion=NON_ABSENT

To learn about all the values, you should look at Jackson’s com.fasterxml.jackson.annotation.JsonInclude.Include enumeration.

Learning 2: Serializing Enum value with Jackson

The second learning that I had today was around how to properly serialize enum values in Jackson. I had enum shown below

public enum Status {
    SUCCESS("success"), ERROR("error");

    private final String status;

    Status(String status) {
        this.status = status;
    }
}

I wanted my JSON structure to be as shown below.

{
  "status": "success"
}

To achieve that you have to Jackson’s @JsonCreator and @JsonValue annotation as shown below.

public enum Status {
    SUCCESS("success"), ERROR("error");

    private final String status;

    @JsonCreator
    Status(String status) {
        this.status = status;
    }

    @JsonValue
    public String getStatus() {
        return this.status;
    }
}

Learning 3: Change remote of a branch in Git

$ git branch develop --set-upstream-to=upstream/develop

You can view remote tracked by local branch using the following command.

$ git branch -lvv

Gson Tip: Using a different FieldNamingPolicy

Today, while write a client for a REST API I had the need to use a different field naming strategy than the default one. By default, when Gson tries to convert a JSON String to an object it expects field names in JSON to be same as field name in the converting object. For example, you can convert following JSON String to Message object as shown below.

{
"message": "hello world!"
}

public class Message{
private String message;
// setters and getters
}

To convert to message JSON to Message object you can use following code.

// String json
Message message = new Gson().fromJson(json, Message.class);

This will not work if JSON has different naming policy(first letter upper case) as shown below.

{
"Message": "hello world!"
}

To make it work, you can use GsonBuilder to create Gson with UPPER_CAMEL_CASE as field naming policy. This is shown below.

Gson gson = new GsonBuilder().setFieldNamingPolicy(FieldNamingPolicy.UPPER_CAMEL_CASE).create();
Message message = gson.fromJson(json, Message.class);