Working with MySQL JSON data type with prepared statements, using it in Go and resolving the problems I had

A short and sweet intro to MySQL JSON data type

As of MySQL 5.7.8, MySQL supports a native JSON data type defined by RFC 7159 which enables efficient access to data in JSON documents. We can obviously store JSON formatted strings in text type column, but there are some advantages of storing that in JSON data type as below:

  • Automatic validation of JSON documents.
  • Optimized storage format.
  • A set of SQL functions is available known as JSON functions to do operations on JSON values such as creation, manipulation, and searching. So that you don’t have to pull down JSON content at application side to do all of this.
  • A set of spatial functions for operating on GeoJSON values is also available.
  • Normalization, Merging, and Auto-wrapping of JSON Values.

All of the above points can be referred in the detail here!


THE USE CASE

I’m going to share the use case of Rental Agreement where we used this feature in PMS (Property Management System) web application.

In the PMS, a rental agreement application holds the information about dates (possession, rent etc…), people (with background detailed information), pets, vehicles, rental apartments, fees and inter-relation between all of those. We have a schema tables for all of these entities. A rental agreement with all detailed information needs to be reviewed and approved by a staff member.

So, initially we thought we can save all of these information with a special flag or negative ID value in actual tables. But considering the scenario that there would be multiple people, pets, vehicles, rental apartments entries for just one rental agreement and saving those in actual tables with the special effect (which would indicate that this thing with X rental agreement is not approved yet) would be big dirty mess up in tables. So, we needed to store these temporary based data somewhere else for all rental agreement which are not approved yet. Hence, I was looking for a solution that if we can leverage something from MySQL itself so we don’t have to do other job or introduce new dependency for saving these temporary based data.

And here it is, MySQL community has introduced a cool feature to store JSON documents with native JSON data type. So, we used this feature with proper structured JSON document to save all of those detailed information of a rental agreement which is not approved yet in a separate table. Once a rental agreement is approved, we can migrate the temporary data from proper structured JSON doc to actual tables after validating and processing it. So, yeah this was the best flexible solution.

Another use case might be considered where you can store configuration (such as settings in json format) in JSON type column.

EXAMPLE

We’re going to take a simple example to understand working with JSON column type using Go (and I used MySQL version 5.7.22). This example has been taken just for the sake of the demo purpose. We will take the JSONDoc structure to store information for json data and Applicant to store applicant’s info. Applicant and JSONDoc tables would be like:

data tables in MySQL | All of the code snippet images created using https://carbon.now.sh tool

and structure for above models in Go

table models in Go

and now you can do queries in MySQL as following:

examples of some JSON functions | JSON_REMOVE, JSON_REPLACE, JSON_EXTRACT
Please, again visit this page to get the details of each JSON function provided by MySQL.

I’m going to share the problems which I faced and eventually I solved by making fixes in the prepared statements.

PROBLEM 1: Accessing specific part of JSON doc by leading $ character using prepared statement

Let’s imagine that you want to replace a key (email, name, cellphone) of an employee using prepared statement. At very first thought I prepared the statement something like this:

UPDATE JSONDoc SET Data = JSON_REPLACE(Data, '$.?', ?) WHERE DocID=?

When I was trying to execute the above prepared statement with passing three parameters (1. JSON path key, 2. Data to update, 3. Doc ID, as all of them you can see to be replaced with ? in above small code snippet in order) it was giving me the following error

sql: expected 2 arguments, got 3

As per this stackoverflow answer? placeholders are not used like string interpolation. They replace entire parameters. When the function argument is a string, the entire argument must be the literal string or built with SQL string functions taking parameters as further arguments. So I had to change the query and the fix was:

UPDATE JSONDoc SET Data = JSON_REPLACE(Data, CONCAT('$', ?), ?) WHERE DocID=?

I would suggest from the experience that it would be best if you can manage to evaluate exact path then instead of doing concatenation with $ leading character in prepared statement, you pass the exact path value in second placeholder so the PS would look like this:

UPDATE JSONDoc SET Data = JSON_REPLACE(Data, ?, ?) WHERE DocID=?

In go program (roughly!), it would look like this

jsonPath := "$.email"
stmt := db.Prepare("UPDATE JSONDoc SET Data = JSON_REPLACE(Data, ?, ?) WHERE DocID=?")
// execute the prepared statement with placeholder(?) data
stmt.Execute(jsonPath, updatedEmail, DocID)

This resolves the parameter substitution problem. But there is still another problem (which was the most painful part I had to deal with) that it caused the data to be interpreted as string data rather than as JSON.

PROBLEM 2: Updating marshalled json data in JSON column properly

In the web service, to take care of JSON data from user inputs, I have another struct ApplicantJSON contains all the field of Applicant except AID. So, even I was setting the json marshalled data in Data field of a JSONDoc struct instance correctly, I was thinking I was doing something wrong in my code due to the problem I had! Code was:

// ApplicantJSON struct
type ApplicantJSON struct {
Name string
Email string
CellPhone string
Address string
}
...
...
/* somewhere in the handler code */
// read request body
var body []byte
body, err = ioutil.ReadAll(r.Body)
if err != nil {
// handle error
}
// unmarshal content to ApplicantJSON
var a ApplicantJSON
err = json.Unmarshal(body, &a)
if err != nil {
// handle error
}
// prepare marshalled content of applicant json
var b []byte
b, err = json.Marshal(&a)
if err != nil {
// handle error
}
// set data in Data field of jDoc
var jDoc JSONDoc
jDoc.Data = b
// now, insert/update data using prepared statement ...

and it was setting data something like in database as following:

"{\"Name\":\"John Doe 1\",\"Email\":\"john@earth.world\",\"CellPhone\":\"xxx0000xxx\",\"Address\":\"Earth, MilkyWay Galaxy :)\"}"

Even I tried this behavior with this tiny python script, thought it could be an issue with json marshalling in Go. But no, even with python script it was the same behavior, setting data in same way in escaped quoted string.

BTW, I still don’t understand this behavior, why programming languages sending escaped quoted data in bytestream to database or why database does not take care of this thing? Looks like it’s doing json encoding twice! Someone can help me here! :)

Later, I found that you can cast the value in json type, as it takes an expression of any type and produces a result value of the specified type. So, the fix was made in the prepared statement itself as following:

UPDATE JSONDoc SET Data = JSON_REPLACE(Data, CONCAT('$', ?), CAST(? AS JSON)) WHERE DocID=?

So, to avoid escaped quoted data and to set data properly in json, CAST it as in JSON type!


Directory Structure

Well, the example app with all of the working code is hosted at this repo. You can find the instructions to run the web application. Here is the directory structure as following:

app directory structure

Explanation

All of the SQL prepared statements are defined in prepsql.go inside the buildStatements function which will be called from inside the InitDB function. Whenever you bootstrap the application, the call to InitDB will be executed and it builds all the prepared statements. Once it succeed, all insert, read, update, delete APIs on db resource can use this prepared statements by DBManager.PrepSQL(look at the struture of DBManager in base.go). Then it will start web application server using net/http package’s http handler.

Screenshots

listing unapproved applicants in table
applicant form to create json doc

I hope you enjoyed reading this article and the experience I shared will help you to avoid the problems, I faced. 😃


To Dos if you’re interested!

If you’ve read the source code, there are commented code in insert/read/prepsql.go files and web service for applicants is not fully implemented (and I intentionally left those!). The commented code will be helpful to do the list of small to-dos. So, here is the list (if you want to do) as following:

  1. Implement API to migrate data from individual JSONDoc to Applicant.
  2. Update UI with Approve button in table row under unapproved Applicants tab to migrate data.
  3. Implement API to list down all approved Applicant and display it on the click of second tab of sidebar.

I’m leaving these parts up to you! No need to create PR, PLEASE! 
Best of luck!! 😃 😃 😃