Sep 19, 2020

Using MySQL JSON data type with Go

tl;dr:

Use json-encoded []byte to write to a JSON column in MySQL
Use json.RawMessage to read a JSON column from MySQL

Recently, I was trying to use MySQL 8.0’s JSON data type within a Go project.
After a long, unsatisfactory search, multiple trial and errors, I found a working solution.
So I thought I’d document it here for future reference.

Bringing up a MySQL server

A simple docker-compose file to setup mysql server:

File: docker-compose.yml

version: '3' 
services:
  db: 
    image: mysql:8.0
    restart: always
    command: --default-authentication-plugin=mysql_native_password
    environment:
      MYSQL_ROOT_PASSWORD: somerandomkeyword
    ports:
      - 3306:3306

Command:

docker-compose up -d

Setup MySQL DB and schema

The following snippet creates a database and a table having one of the columns with JSON data type:

File: db.sql

DROP DATABASE IF EXISTS scoringdb;

CREATE DATABASE scoringdb;

USE scoringdb;

DROP TABLE IF EXISTS gamestats;

CREATE TABLE `gamestats`(
    `stat_id` int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `player` varchar(128) NOT NULL,
    `yards` int(10) NOT NULL,
    `longest` int(10) NOT NULL,
    `touchdowns` int(10) NOT NULL,
    `misc` JSON,
    INDEX `idx_player` (`player`),
    INDEX `idx_yards` (`yards`),
    INDEX `idx_longest` (`longest`),
    INDEX `idx_touchdowns` (`touchdowns`)
) ENGINE=InnoDB CHARACTER SET utf8;

FLUSH TABLES;

Command:

mysql -uroot -psomerandomkeyword -h 127.0.0.1 < db.sql

A schema like this can be useful when there is a huge chunk of metadata, but only a few fields need indexing.

Writing to a JSON column

A sample gaming stats struct for reference:
The interface{} fields are raw input, received from a dump file and the corresponding int fields are parsed values for them.

File: contract.go

type Stat struct {
    Player             string      `json:"Player"`
    Team               string      `json:"Team"`
    Position           string      `json:"Pos"`
    Attempts           int         `json:"Att"`
    AttemptsPerGameAvg float64     `json:"Att/G"`
    Yards              interface{} `json:"Yds"`
    YardsParsed        int         `json:"-"`
    AvgYardsPerAttempt float64     `json:"Yds/G"`
    Touchdowns         int         `json:"TD"`
    Longest            interface{} `json:"Lng"`
    LongestParsed      int         `json:"-"`
    LongestIsTouchdown bool        `json:"-"`
    FirstDown          int         `json:"1st"`
    PerFirstDown       float64     `json:"1st%"`
    Yards20            int         `json:"20+"`
    Yards40            int         `json:"40+"`
    Fumbles            int         `json:"FUM"`
}

type Stats []Stat

To write above struct into the table schema we defined earlier,

standard fields get written as is,
for the JSON column, a json-encoded []byte is needed.

File: model.go

func InsertGameStats(s Stat) error {
    js, err := json.Marshal(s)
    if err != nil {
        return err 
    }
    stmt := "INSERT INTO `gamestats`"
    stmt += "(player,yards,longest,touchdowns,misc) VALUES (?,?,?,?,?)"
    _, err = db.Exec(stmt, s.Player, s.YardsParsed, s.LongestParsed, s.Touchdowns, js) 
    return err
}

Reading the values from a JSON column

For reading and parsing the passed data, we’ll need to use json.RawMessage type.

File: contract.go

func FetchStats() (Stats, error) {
    stmt := "SELECT misc from `gamestats` limit 10"
    var ss Stats
    rows, err := db.Query(stmt)
    if err != nil {
        return nil, err
    }
    defer rows.Close()
    for rows.Next() {
        var data json.RawMessage // for JSON column type
        var stat Stat
        if err = rows.Scan(&data); err != nil {
            return nil, err
        }
        if err = json.Unmarshal(data, &stat); err != nil {
            return nil, err
        }
        ss = append(ss, stat)
    }
    if err = rows.Err(); err != nil {
        return nil, err
    }
    return ss, nil
}

And that’s it!
To see a working version of this app, checkout the full project here: https://github.com/geekgunda/nfl-rushing