Before you go, check out these stories!

0
Hackernoon logoGo: Handling JSON in MySQL by@tiago-melo

Go: Handling JSON in MySQL

Author profile picture

@tiago-meloTiago Melo

Senior Software Engineer

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.

Download the source

Here:Ā https://bitbucket.org/tiagoharris/mysql-with-json

Tags

Join Hacker Noon

Create your free account to unlock your custom reading experience.