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

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 JSONcolumn 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 JSONDocstructure to store information for json data and Applicant to store applicant’s info. Applicant and JSONDoc tables would be like:

and structure for above models in Go

and now you can do queries in MySQL as following:

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('

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.

Also read: Dynamic Serializer Selection for Django Rest Framework Viewsets Based on User Roles

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('

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

Also read: Automated Installation of MySQL 5.7 on Amazon EC2 Instances Running Amazon Linux AMI

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:

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

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