In this article, we'll explain how to read/write data from/into a table. JSON MySQL 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: : PK that uniquely identifies the event id : event's name name : event's properties properties : specification of the browser that visitors use to browse the website browser The JSON datatype was introduced in MySQL 5.7. This is the for our table: DDL ( auto_increment primary , ( ), properties , browser ); CREATE TABLE events id int key name varchar 255 json json If we were to manually insert records, we could do it like this: (event_name, properties, browser) ( , , ), ( , , ), ( , , ), ( , , ), ( , , ), ( , , ); INSERT INTO events 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 ( ). Let's play with browser's name: path operator -> , browser-> browser ; SELECT id '$.name' 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 ( sec) ----+-----------+ ----+-----------+ ----+-----------+ set 0.00 Notice that data in the browser column is surrounded by quote marks. To remove the quote marks, we use the ( ) like this: inline path operator ->> , browser->> browser ; SELECT id '$.name' 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 ( sec) ----+---------+ ----+---------+ ----+---------+ set 0.00 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: browser->> browser, (browser) browser->> ; SELECT '$.name' count FROM events GROUP BY '$.name' The output of the query is as follows: + | browser | count(browser) | + | Safari | 2 | | Firefox | 3 | | Chrome | 1 | + 3 rows in ( sec) ---------+----------------+ ---------+----------------+ ---------+----------------+ set 0.02 The code Now let's see how we can work with these JSON fields in , step by step. Go First, let's define the struct we'll use to represent a record in "events" table: ( StringInterfaceMap [ ] {} Event { Id Name Properties StringInterfaceMap Browser StringInterfaceMap } ) type map string interface struct int `json:"id"` string `json:"name"` `json:"properties"` `json:"browser"` Notice that in our "Event" struct, we defined both "Properties" and "Browser" as our "StringInferfaceMap", which is map of string keys that can store pretty much any kind of information (int, string, other structs, and so on). named type Next, let's create some variables to hold the SQL queries we'll use: ( insertEventQuery = selectEventByIdQuery = ) var `INSERT INTO events(name, properties, browser) values (?, ?, ?)` `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 interface returns an error and not the converted value: Scanner Scanner { Scan(src {}) error } type interface interface Thus, the implementor of this interface should have a which will mutate its value upon successful conversion. pointer receiver When implementing this interface, we need to convert the slice into a slice first, then we call 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: uint8 byte json.Unmarshal() { source [] _m := ( [ ] {}) src.( ) { [] : source = [] (src.([] )) : : errors.New( ) } err := json.Unmarshal(source, &_m) err != { err } *m = StringInterfaceMap(_m) } func (m *StringInterfaceMap) Scan (src {}) interface error var byte make map string interface switch type case uint8 byte uint8 case nil return nil default return "incompatible type for StringInterfaceMap" if nil return 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 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 and do the appropriate conversion: Valuer json.Marshal() { (m) == { , } j, err := json.Marshal(m) err != { , err } driver.Value([] (j)), } func (m StringInterfaceMap) Value () (driver.Value, error) if len 0 return nil nil if nil return nil return byte nil This is the full source code: main ( _ ) ( StringInterfaceMap [ ] {} Event { Id Name Properties StringInterfaceMap Browser StringInterfaceMap } ) ( insertEventQuery = selectEventByIdQuery = ) { (m) == { , } j, err := json.Marshal(m) err != { , err } driver.Value([] (j)), } { source [] _m := ( [ ] {}) src.( ) { [] : source = [] (src.([] )) : : errors.New( ) } err := json.Unmarshal(source, &_m) err != { err } *m = StringInterfaceMap(_m) } { res, err := db.Exec(insertEventQuery, event.Name, event.Properties, event.Browser) err != { , err } lid, err := res.LastInsertId() err != { , err } lid, } { row := db.QueryRow(selectEventByIdQuery, id) err := row.Scan(&event.Id, &event.Name, &event.Properties, &event.Browser) err != { err } } { fmt.Sprintf( , dbUser, dbPassword, conn, dbName) } {} { [ ] {}{ : , } } {} { [ ] {}{ : , : , : { X Y }{ , }, } } { dns := getDNSString( , , , ) db, err := sql.Open( , dns) err != { (err) } err = db.Ping() err != { (err) } db.Close() event := Event{ Name: , Properties: buildPropertiesData(), Browser: buildBrowserData(), } insertedId, err := insertEvent(db, event) err != { (err) } firstEvent := Event{} err = selectEventById(db, insertedId, &firstEvent) err != { (err) } fmt.Println( ) fmt.Println( , firstEvent.Id) fmt.Println( , firstEvent.Name) fmt.Println( , firstEvent.Properties) fmt.Println( , firstEvent.Browser) fmt.Println( ) j, err := json.Marshal(firstEvent) err != { (err) } fmt.Println( (j)) } package import "database/sql" "database/sql/driver" "encoding/json" "errors" "fmt" "github.com/go-sql-driver/mysql" type map string interface struct int `json:"id"` string `json:"name"` `json:"properties"` `json:"browser"` var `INSERT INTO events(name, properties, browser) values (?, ?, ?)` `SELECT * FROM events WHERE id = ?` func (m StringInterfaceMap) Value () (driver.Value, error) if len 0 return nil nil if nil return nil return byte nil func (m *StringInterfaceMap) Scan (src {}) interface error var byte make map string interface switch type case uint8 byte uint8 case nil return nil default return "incompatible type for StringInterfaceMap" if nil return return nil func insertEvent (db *sql.DB, event Event) ( , error) int64 if nil return 0 if nil return 0 return nil func selectEventById (db *sql.DB, id , event *Event) int64 error if nil return return nil func getDNSString (dbName, dbUser, dbPassword, conn ) string string return "%s:%s@tcp(%s)/%s?parseTime=true&timeout=60s&readTimeout=60s" [ ] 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 int `json:"x"` int `json:"y"` 1920 1080 func main () "tutorial" "root" "tutorial" "localhost:3310" "mysql" if nil panic if nil panic defer "pageview" if nil panic if nil panic "\nEvent fields:\n" "Id: " "Name: " "Properties: " "Browser: " "\nJSON representation:\n" if nil panic string Running it If you've read my article about , you'll be familiar with the way I'm setting our database, through Docker Compose and golang-migrate tools. database migrations in Go MySQL To run it, let's issue "make run": tiago:~/develop/go/articles/mysql-with-json$ make run Setting up MySQL... Creating volume with driver Creating db ... MySQL not ready, sleeping 5 secs... MySQL not ready, sleeping 5 secs... MySQL not ready, sleeping 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: { :1, : , :{ : }, name Safari os Mac resolution x y local "mysql-with-json_db-data" local done for for for "id" "name" "pageview" "properties" "page" "/" "browser {" ":" "," ":" "," ":{" ":1920," ":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 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 -it db /bin/bash root@288d45771919:/ Enter password: Reading table information 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 or . Type to clear the current input statement. mysql> select * from events; +----+----------+---------------+-----------------------------------------------------------------------+ | id | name | properties | browser | +----+----------+---------------+-----------------------------------------------------------------------+ | 1 | pageview | { : } | { : , : , : { : 1920, : 1080}} | +----+----------+---------------+-----------------------------------------------------------------------+ "docker-entrypoint.s…" exec # mysql -uroot -p -D tutorial for 'help;' '\h' for help '\c' "page" "/" "os" "Mac" "name" "Safari" "resolution" "x" "y" - 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 5.6 does not recognize the "json" datatype, all you need to do is to use the "text" type and it will work: MySQL ( auto_increment primary , ( ), properties , browser ); CREATE TABLE events id int key name varchar 255 text text Conclusion In this article we saw how we can leverage the use of interfaces in so complex operations like reading/writing to a table doesn't pollute our code. Go JSON MySQL Download the source Here: https://bitbucket.org/tiagoharris/mysql-with-json
