Handle JSON datatype in MySQL using GORM

While working on a project, I recently bumped into JSON datatype. The JSON datatype is supported by MySQL and carries advantages such as automatic validation of JSON documents and optimized storage format. In this article, we will be using GORM to work with JSON datatype in MySQL.

First, let’s create a table to work with. The DDL for the table we will be using in this article is as below:

CREATE TABLE `customers` (
  `customer_name` varchar(50) NOT NULL,
  `customer_type` varchar(45) NOT NULL,
  `customer_info` json NOT NULL,
  PRIMARY KEY (`customer_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

After we have created a table named, customers in our database, we will be working with the table using GORM.

We need to import the library for datatypes to work with JSON datatypes.

Then, we need to create a struct of similar fields to our table.

Then, we need to create a struct of similar fields to our table.

type Customer struct {
   CustomerName string
   CustomerType string
   CustomerInfo datatypes.JSON
}

In the Customer struct above, we have fields CustomerName and CustomerType of string type and CustomerInfo of JSON type.

Finally, let’s have a look at various operations that we can perform.

Inserting into the table:

DB.Create(&Customer{
   CustomerName: "Customer-1",
   CustomerType: "Type-1",
   CustomerInfo: []byte(`{"age": 18, "tags": ["tag1", "tag2"], "address": {"city": "dharan", "district":"sunsari"}}`),
})

Finding data from the table:

1. To find an entry using the key in the column with JSON data

var customerDetail Customer
DB.Find(&customerDetail, datatypes.JSONQuery("customer_info").HasKey("age"))

Image: Screenshot of customerDetail printed in console

2. To find an entry with multiple keys in the column with JSON data

var customerDetail Customer
DB.Find(&customerDetail, datatypes.JSONQuery("customer_info").HasKey("address", "city"))

Image: Screenshot of customerDetail printed in console

3. To check JSON extract value from keys equal to the value

//extracting value with a single key
DB.Find(&customerDetail, datatypes.JSONQuery("customer_info").Equals(18, "age"))


//extracting value with multiple keys
DB.Find(&customerDetail, datatypes.JSONQuery("customer_info").Equals("dharan", "address", "city"))

Set fields of JSON column

1. Set a value on existing key

DB.Model(&Customer{}).Where("customer_name = ?", "Customer-1").UpdateColumn("customer_info", datatypes.JSONSet("customer_info").Set("age", 20))

Since the field ageexists for the entry where customer_name has the value Customer-1, it updates the value of ageto 20.

2. Set a new key with a value

DB.Model(&Customer{}).Where("customer_name = ?", "Customer-1").UpdateColumn("customer_info", datatypes.JSONSet("customer_info").Set("gender", "female"))

In this case, the field genderdoes not exist for the entry where customer_name has the value Customer-1, so this statement adds a new field gender as a key and female as its value.

There is also another scenario where you might want to have pre-defined fields for the keys in a column with JSON datatype. In such cases, we can use struct embedding.

We will be using a struct similar to the one we have used above but with some changes.

type Customer struct {
   CustomerName string
   CustomerType string
   CustomerInfo CustomerInfo
}

type CustomerInfo struct {
   Age     int16  
   Gender  string 
   Address string
}

However, we are not using datatypes.JSON the datatype of CustomerInfo and instead embedding another struct with the same name. Therefore, we have to implement Scanner / Valuer.

Scanner / Valuer interfaces help GORM to receive/save the customized data type into the database.

import (
 "database/sql/driver"
 "encoding/json"
 "errors"
)

//Value returns value of CustomerInfo struct and implements driver.Valuer interface
func (c CustomerInfo) Value() (driver.Value, error) {
   return json.Marshal(c)
}


//Scan scans value into Jsonb and implements sql.Scanner interface
func (c *CustomerInfo) Scan(value interface{}) error {
   b, ok := value.([]byte)
   if !ok {
      return errors.New("type assertion to []byte failed")
   }
   return json.Unmarshal(b, &c)
}

After this, we can now smoothly operate our database.

Inserting into the table:

DB.Create(&Customer{
   CustomerName: "Customer-2",
   CustomerType: "Type-1",
   CustomerInfo: CustomerInfo{
      Age:     25,
      Gender:  "male",
      Address: "province-1",
   },
})

We can also find data, check values, and set fields of JSON columns by using the same approach as mentioned in the earlier part of this article.

I appreciate your reading, and I hope this article was helpful.