11,172 reads

Go: Handling JSON in MySQL

by Tiago MeloJanuary 13th, 2021

Too Long; Didn't Read

Company Mentioned

featured image - Go: Handling JSON in MySQL

In this article, we'll explain how to read/write JSON data from/into a MySQL table.

Motivation

Sometimes you can find yourself in a situation where you'd like to use a hybrid approach: what if you could structure some parts of your database and leave others to be flexible?

Suppose we want to track the actions taken on a given website. We'll create a table called "events" to hold that information:

id: PK that uniquely identifies the event

name: event's name

properties: event's properties

browser: specification of the browser that visitors use to browse the website

The JSON datatype was introduced in MySQL 5.7. This is the DDL for our table:

CREATE TABLE events( 
  id int auto_increment primary key, 
  name varchar(255), 
  properties json, 
  browser json
);

If we were to manually insert records, we could do it like this:

INSERT INTO events(event_name, properties, browser) 
VALUES (
  'pageview', 
   '{ "page": "/" }',
   '{ "name": "Safari", "os": "Mac", "resolution": { "x": 1920, "y": 1080 } }'
),
('pageview', 
  '{ "page": "/contact" }',
  '{ "name": "Firefox", "os": "Windows", "resolution": { "x": 2560, "y": 1600 } }'
),
(
  'pageview', 
  '{ "page": "/products" }',
  '{ "name": "Safari", "os": "Mac", "resolution": { "x": 1920, "y": 1080 } }'
),
(
  'purchase', 
  '{ "amount": 200 }',
  '{ "name": "Firefox", "os": "Windows", "resolution": { "x": 1600, "y": 900 } }'
),
(
  'purchase', 
  '{ "amount": 150 }',
  '{ "name": "Firefox", "os": "Windows", "resolution": { "x": 1280, "y": 800 } }'
),
(
  'purchase', 
  '{ "amount": 500 }',
  '{ "name": "Chrome", "os": "Windows", "resolution": { "x": 1680, "y": 1050 } }'
);

To pull values out of the JSON columns, we use the column path operator (->). Let's play with browser's name:

SELECT id, browser->'$.name' browser
FROM events;

This query returns the following output:

+----+-----------+
| id | browser   |
+----+-----------+
|  1 | "Safari"  |
|  2 | "Firefox" |
|  3 | "Safari"  |
|  4 | "Firefox" |
|  5 | "Firefox" |
|  6 | "Chrome"  |
+----+-----------+
6 rows in set (0.00 sec)

Notice that data in the browser column is surrounded by quote marks. To remove the quote marks, we use the inline path operator (->>) like this:

SELECT id, browser->>'$.name' browser
FROM events;

As we can see in the following output, the quote marks were removed:

+----+---------+
| id | browser |
+----+---------+
|  1 | Safari  |
|  2 | Firefox |
|  3 | Safari  |
|  4 | Firefox |
|  5 | Firefox |
|  6 | Chrome  |
+----+---------+
6 rows in set (0.00 sec)

As you've imagined, we can use the path operator like any other field type. For example, to get the browser usage, we can use the following statement:

SELECT browser->>'$.name' browser, count(browser)
FROM events
GROUP BY browser->>'$.name';

The output of the query is as follows:

+---------+----------------+
| browser | count(browser) |
+---------+----------------+
| Safari  |              2 |
| Firefox |              3 |
| Chrome  |              1 |
+---------+----------------+
3 rows in set (0.02 sec)

The code

Now let's see how we can work with these JSON fields in Go, step by step.

First, let's define the struct we'll use to represent a record in "events" table:

type (
	StringInterfaceMap map[string]interface{}
	Event              struct {
		Id         int                `json:"id"`
		Name       string             `json:"name"`
		Properties StringInterfaceMap `json:"properties"`
		Browser    StringInterfaceMap `json:"browser"`
	}
)

Notice that in our "Event" struct, we defined both "Properties" and "Browser" as our named type "StringInferfaceMap", which is map of string keys that can store pretty much any kind of information (int, string, other structs, and so on).

Next, let's create some variables to hold the SQL queries we'll use:

var (
	insertEventQuery = `INSERT INTO events(name, properties, browser) values (?, ?, ?)`
	selectEventByIdQuery = `SELECT * FROM events WHERE id = ?`
)

Now, the interesting part: how do we do to read the JSON columns? how do we persist them?

You're right: we need a way of customizing the way that "properties" and "browser" JSON columns are read and written. We could write some helper functions to do the work, but it wouldn't be a clean approach. What if we could use interfaces to come up with a cleaner design?

The read operation

Go provides the Scanner interface to do the data type conversion while scanning.

The signature of the Scanner interface returns an error and not the converted value:

type Scanner interface {  
  Scan(src interface{}) error
}

Thus, the implementor of this interface should have a pointer receiver which will mutate its value upon successful conversion.

When implementing this interface, we need to convert the uint8 slice into a byte slice first, then we call json.Unmarshal() so we can convert it in map[string]interface{}. If the conversion is successful, we need to assign the converted value to the receiver "StringInterfaceMap". Here's our full implementation:

func (m *StringInterfaceMap) Scan(src interface{}) error {
	var source []byte
	_m := make(map[string]interface{})


	switch src.(type) {
	case []uint8:
		source = []byte(src.([]uint8))
	case nil:
		return nil
	default:
		return errors.New("incompatible type for StringInterfaceMap")
	}
	err := json.Unmarshal(source, &_m)
	if err != nil {
		return err
	}
	*m = StringInterfaceMap(_m)
	return nil
}

Note that we are handling "null" values as well, as both columns are nullable. If that's the case, it will be translated as an empty map.

The write operation

Like the Scanner interface, Go provides the Valuer interface that we need to implement to do the type conversion. We first check if the map is empty; if it's the case, it will insert "null" into the respective column. Otherwise, it will call json.Marshal() and do the appropriate conversion:

func (m StringInterfaceMap) Value() (driver.Value, error) {
	if len(m) == 0 {
		return nil, nil
	}
	j, err := json.Marshal(m)
	if err != nil {
		return nil, err
	}
	return driver.Value([]byte(j)), nil
}

This is the full source code:

package main


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


	_ "github.com/go-sql-driver/mysql"
)


type (
	StringInterfaceMap map[string]interface{}
	Event              struct {
		Id         int                `json:"id"`
		Name       string             `json:"name"`
		Properties StringInterfaceMap `json:"properties"`
		Browser    StringInterfaceMap `json:"browser"`
	}
)


var (
	insertEventQuery     = `INSERT INTO events(name, properties, browser) values (?, ?, ?)`
	selectEventByIdQuery = `SELECT * FROM events WHERE id = ?`
)


func (m StringInterfaceMap) Value() (driver.Value, error) {
	if len(m) == 0 {
		return nil, nil
	}
	j, err := json.Marshal(m)
	if err != nil {
		return nil, err
	}
	return driver.Value([]byte(j)), nil
}


func (m *StringInterfaceMap) Scan(src interface{}) error {
	var source []byte
	_m := make(map[string]interface{})


	switch src.(type) {
	case []uint8:
		source = []byte(src.([]uint8))
	case nil:
		return nil
	default:
		return errors.New("incompatible type for StringInterfaceMap")
	}
	err := json.Unmarshal(source, &_m)
	if err != nil {
		return err
	}
	*m = StringInterfaceMap(_m)
	return nil
}


func insertEvent(db *sql.DB, event Event) (int64, error) {
	res, err := db.Exec(insertEventQuery, event.Name, event.Properties, event.Browser)
	if err != nil {
		return 0, err
	}
	lid, err := res.LastInsertId()
	if err != nil {
		return 0, err
	}
	return lid, nil
}


func selectEventById(db *sql.DB, id int64, event *Event) error {
	row := db.QueryRow(selectEventByIdQuery, id)
	err := row.Scan(&event.Id, &event.Name, &event.Properties, &event.Browser)
	if err != nil {
		return err
	}
	return nil
}


func getDNSString(dbName, dbUser, dbPassword, conn string) string {
	return fmt.Sprintf("%s:%s@tcp(%s)/%s?parseTime=true&timeout=60s&readTimeout=60s",
		dbUser,
		dbPassword,
		conn,
		dbName)
}


func buildPropertiesData() map[string]interface{} {
	return map[string]interface{}{
		"page": "/",
	}
}


func buildBrowserData() map[string]interface{} {
	return map[string]interface{}{
		"name": "Safari",
		"os":   "Mac",
		"resolution": struct {
			X int `json:"x"`
			Y int `json:"y"`
		}{1920, 1080},
	}
}


func main() {
	dns := getDNSString("tutorial", "root", "tutorial", "localhost:3310")
	db, err := sql.Open("mysql", dns)
	if err != nil {
		panic(err)
	}
	err = db.Ping()
	if err != nil {
		panic(err)
	}
	defer db.Close()


	event := Event{
		Name:       "pageview",
		Properties: buildPropertiesData(),
		Browser:    buildBrowserData(),
	}


	insertedId, err := insertEvent(db, event)
	if err != nil {
		panic(err)
	}


	firstEvent := Event{}
	err = selectEventById(db, insertedId, &firstEvent)
	if err != nil {
		panic(err)
	}


	fmt.Println("\nEvent fields:\n")


	fmt.Println("Id:         ", firstEvent.Id)
	fmt.Println("Name:       ", firstEvent.Name)
	fmt.Println("Properties: ", firstEvent.Properties)
	fmt.Println("Browser:    ", firstEvent.Browser)


	fmt.Println("\nJSON representation:\n")


	j, err := json.Marshal(firstEvent)
	if err != nil {
		panic(err)
	}
	fmt.Println(string(j))
}

Running it

If you've read my article about database migrations in Go, you'll be familiar with the way I'm setting our MySQL database, through Docker Compose and golang-migrate tools.

To run it, let's issue "make run":

tiago:~/develop/go/articles/mysql-with-json$ make run

Setting up local MySQL...

Creating volume "mysql-with-json_db-data" with local driver

Creating db ... done

MySQL not ready, sleeping for 5 secs...

MySQL not ready, sleeping for 5 secs...

MySQL not ready, sleeping for 5 secs...

... MySQL is up and running!

Running migrations...

1/u events (42.932122ms)




Event fields:




Id:          1

Name:        pageview

Properties:  map[page:/]

Browser:     map[name:Safari os:Mac resolution:map[x:1920 y:1080]]




JSON representation:




{"id":1,"name":"pageview","properties":{"page":"/"},"browser {"name":"Safari","os":"Mac","resolution":{"x":1920,"y":1080}}}

- invoked docker-compose to setup our MySQL container;

- waited for it to be ready, and then migrated the database;

- inserted a sample event in "events" table;

- used the returned id from the inserted event to query it up;

- then we did the output for both fields and for the struct's JSON representation.

Now let's check our table:

tiago:~/develop/go/articles/mysql-with-json$ docker ps

CONTAINER ID   IMAGE       COMMAND                  CREATED              STATUS              PORTS                                         NAMES

288d45771919   mysql:5.7   "docker-entrypoint.s…"   About a minute ago   Up About a minute   3310/tcp, 33060/tcp, 0.0.0.0:3310->3306/tcp   db

tiago:~/develop/go/articles/mysql-with-json$ docker exec -it db /bin/bash

root@288d45771919:/# mysql -uroot -p -D tutorial

Enter password: 

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A




Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 6

Server version: 5.7.32 MySQL Community Server (GPL)




Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.




Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.




Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.




mysql> select * from events;

+----+----------+---------------+-----------------------------------------------------------------------+

| id | name     | properties    | browser                                                               |

+----+----------+---------------+-----------------------------------------------------------------------+

|  1 | pageview | {"page": "/"} | {"os": "Mac", "name": "Safari", "resolution": {"x": 1920, "y": 1080}} |

+----+----------+---------------+-----------------------------------------------------------------------+

- connected to our MySQL container

- used its mysql client to connect to the database

- queried the table

Pretty cool!

Running it in MySQL 5.6

Since MySQL 5.6 does not recognize the "json" datatype, all you need to do is to use the "text" type and it will work:

CREATE TABLE events( 
  id int auto_increment primary key, 
  name varchar(255), 
  properties text, 
  browser text
);

Conclusion

In this article we saw how we can leverage the use of interfaces in Go so complex operations like reading/writing JSON to a MySQL table doesn't pollute our code.