working with MySQL JSON type in prepared statements using Go

directory_structure.txt

go-mysql-json-ex/
|-- dbm/	// contains all database related operations
|   |-- internal/	// internal only accessible to dbm
|   |   |-- base.go			// contains db conf, manager
|   |   |-- prepsql.go		// all prepared statements used in entire app
|   |-- conn.go				// db init, close API call
|   |-- delete.go			// delete db resource APIs 
|   |-- get.go				// get db resource APIs
|   |-- insert.go			// insert db resource APIs
|   |-- models.go			// models in go representing db tables structrure
|   |-- read.go				// read data from db resource APIs
|   |-- scheam.sql			// schema.sql - table definitions
|   |-- update.go			// update db resource APIs
|-- static/	// ALL static css, js, html files
|   |-- css/	
|   |   |-- bootstrap-reboot.min.css
|   |   |-- bootstrap-reboot.min.css.map
|   |   |-- bootstrap.min.css
|   |   |-- bootstrap.min.css.map
|   |   |-- style.css
|   |-- js/				
|   |   |-- api.js			// fetch APIs for server interaction
|   |   |-- app.js			// UI interactions handlers
|   |   |-- bootstrap.min.js
|   |   |-- bootstrap.min.js.map
|   |   |-- jquery.min.js
|   |-- index.html			// HTML template for app
|-- ws/	// all web service handler 
|   |-- applicants.go		// handler for applicants (will be part of exercise)
|   |-- base.go				// common functions for web services
|   |-- jsondocs.go			// handler for jsondocs API
|-- Makefile				// app build, clean, schema init
|-- main.go					// web application server

models.go

// Applicant struct
type Applicant struct {
	AID       int64
	Name      string
	Email     string
	CellPhone string
	Address   string
}

// JSONDoc struct
type JSONDoc struct {
	DocID int64
	Data  json.RawMessage
}

statements_example_1.sql

INSERT INTO JSONDoc(Data) VALUES('{"AID": 1, "Name": "Harry Doe", "Email": "[email protected]", "CellPhone": "xxx0000xxx", "Address": "Earth, Milky Way Galaxy!", "RMX": "Fool"}');
-- Query OK, 1 row affected (0.01 sec)

/* get content after insert operation of json doc */
SELECT * FROM JSONDoc WHERE DocID=1;
-- +-------+------------------------------------------------------------------------------------------------------------------------------------------------+
-- | DocID | Data                                                                                                                                           |
-- +-------+------------------------------------------------------------------------------------------------------------------------------------------------+
-- |     1 | {"AID": 1, "RMX": "Fool", "Name": "Harry Doe", "Email": "[email protected]", "Address": "Earth, Milky Way Galaxy!", "CellPhone": "xxx0000xxx"} |
-- +-------+------------------------------------------------------------------------------------------------------------------------------------------------+
-- 1 row in set (0.00 sec)

/* remove RMX key from json doc using leading $ with the correct path*/
/*
   JSON_REMOVE: 1st arg - JSON DOC, 2nd arg - correct path to remove
   content!
*/
UPDATE JSONDoc SET Data = JSON_REMOVE(Data, '$.RMX') WHERE DocID=1;
-- Query OK, 1 row affected (0.02 sec)
-- Rows matched: 1  Changed: 1  Warnings: 0

/* get content after removing `RMX` key from the json doc */ 
SELECT * FROM JSONDoc WHERE DocID=1;
-- +-------+---------------------------------------------------------------------------------------------------------------------------------+
-- | DocID | Data                                                                                                                            |
-- +-------+---------------------------------------------------------------------------------------------------------------------------------+
-- |     1 | {"AID": 1, "Name": "Harry Doe", "Email": "[email protected]", "Address": "Earth, Milky Way Galaxy!", "CellPhone": "xxx0000xxx"} |
-- +-------+---------------------------------------------------------------------------------------------------------------------------------+
-- 1 row in set (0.00 sec)

/* update Name, Email in the json doc */
/* 
   JSON_REPLACE expects first argument is the json doc to update
   and rest of the argument follows {path, value},... fashion.
*/
UPDATE JSONDoc SET Data = JSON_REPLACE(Data, '$.Name', 'John Doe', '$.Email', '[email protected]') WHERE DocID=1;
-- Query OK, 1 row affected (0.02 sec)
-- Rows matched: 1  Changed: 1  Warnings: 0

/* get content after updating Name, Email info in the json doc */
SELECT * FROM JSONDoc WHERE DocID=1;
-- +-------+-------------------------------------------------------------------------------------------------------------------------------+
-- | DocID | Data                                                                                                                          |
-- +-------+-------------------------------------------------------------------------------------------------------------------------------+
-- |     1 | {"AID": 1, "Name": "John Doe", "Email": "[email protected]", "Address": "Earth, Milky Way Galaxy!", "CellPhone": "xxx0000xxx"} |
-- +-------+-------------------------------------------------------------------------------------------------------------------------------+
-- 1 row in set (0.00 sec)

/* get the extracted info from the json doc */
/* extract single item -- Name */
SELECT JSON_EXTRACT(Data, '$.Name') FROM JSONDoc WHERE DocID=1;
-- +------------------------------+
-- | JSON_EXTRACT(Data, '$.Name') |
-- +------------------------------+
-- | "John Doe"                   |
-- +------------------------------+
-- 1 row in set (0.00 sec)

/* get the extracted info from the json doc */
/* extract multiple item, returns in an array -- Name, Email */
SELECT JSON_EXTRACT(Data, '$.Name', '$.Email') FROM JSONDoc WHERE DocID=1;
-- +-----------------------------------------+
-- | JSON_EXTRACT(Data, '$.Name', '$.Email') |
-- +-----------------------------------------+
-- | ["John Doe", "[email protected]"]        |
-- +-----------------------------------------+
-- 1 row in set (0.01 sec)

tables.sql

CREATE TABLE Applicant (
    AID BIGINT(20) NOT NULL AUTO_INCREMENT,
    Name VARCHAR(100) NOT NULL DEFAULT '',
    Email VARCHAR(100) NOT NULL DEFAULT '',
    CellPhone VARCHAR(100) NOT NULL DEFAULT '',
    Address VARCHAR(100) NOT NULL DEFAULT '',
    PRIMARY KEY (AID)
);

CREATE TABLE JSONDoc (
    DocID BIGINT(20) NOT NULL AUTO_INCREMENT,
    Data JSON DEFAULT NULL,
    PRIMARY KEY (DocID)
);