Essentially, I am trying to run a query on a MySQL database, get the data made converted into JSON and sent back to the client. I have tried several methods and all of the "easy" ones result in sending back all of the JSON as a string. I need this to be send back as a key (string
) with []float64
value. This way I have an array of data associated with a key. Also, this needs to have a type. The best method I've found so far to accomplish this was to build put all of the data into a struct, encode it and send that back to the ResponseWriter
.
I have seen several questions on making JSON from a database but I haven't found anything utilizing the struct method. I wrote the below code into a single function to illustrate my question. This is VERY limited in that it will only handle two fields and it MUST be a float64
.
Therefore, my question is: How do I create this JSON from a query response that has the correct type before sending this back to the client and is there a way to do this dynamically (ie, can accept a variable number of columns and unknown types)?:
{ "Values":[12.54, 76.98, 34.90], "Dates": ["2017-02-03", "2017-02-04:, "2017-02-05"]}
type DbDao struct{
db *sql.DB
}
type JSONData struct {
Values []float64
Dates []string
}
func (d *DbDao) SendJSON(sqlString string, w http.ResponseWriter) (error) {
stmt, err := d.db.Prepare(sqlString)
if err != nil {
return err
}
defer stmt.Close()
rows, err := stmt.Query()
if err != nil {
return err
}
defer rows.Close()
values := make([]interface{}, 2)
scanArgs := make([]interface{}, 2)
for i := range values {
scanArgs[i] = &values[i]
}
for rows.Next() {
err := rows.Scan(scanArgs...)
if err != nil {
return err
}
var tempDate string
var tempValue float64
var myjson JSONData
d, dok := values[0].([]byte)
v, vok := values[1].(float64)
if dok {
tempDate = string(d)
if err != nil {
return err
}
myjson.Dates = append(myjson.Dates, tempDate)
}
if vok {
tempValue = v
myjson.Values = append(myjson.Values, tempValue)
fmt.Println(v)
fmt.Println(tempValue)
}
err = json.NewEncoder(w).Encode(&myjson)
if err != nil {
return err
}
}
return nil
}
This is a much better way to do it (Tested with Postgres). No reflect/reflection needed:
columnTypes, err := rows.ColumnTypes()
if err != nil {
return err
}
count := len(columnTypes)
finalRows := []interface{}{};
for rows.Next() {
scanArgs := make([]interface{}, count)
for i, v := range columnTypes {
switch v.DatabaseTypeName() {
case "VARCHAR", "TEXT", "UUID", "TIMESTAMP":
scanArgs[i] = new(sql.NullString)
break;
case "BOOL":
scanArgs[i] = new(sql.NullBool)
break;
case "INT4":
scanArgs[i] = new(sql.NullInt64)
break;
default:
scanArgs[i] = new(sql.NullString)
}
}
err := rows.Scan(scanArgs...)
if err != nil {
return err
}
masterData := map[string]interface{}{}
for i, v := range columnTypes {
if z, ok := (scanArgs[i]).(*sql.NullBool); ok {
masterData[v.Name()] = z.Bool
continue;
}
if z, ok := (scanArgs[i]).(*sql.NullString); ok {
masterData[v.Name()] = z.String
continue;
}
if z, ok := (scanArgs[i]).(*sql.NullInt64); ok {
masterData[v.Name()] = z.Int64
continue;
}
if z, ok := (scanArgs[i]).(*sql.NullFloat64); ok {
masterData[v.Name()] = z.Float64
continue;
}
if z, ok := (scanArgs[i]).(*sql.NullInt32); ok {
masterData[v.Name()] = z.Int32
continue;
}
masterData[v.Name()] = scanArgs[i]
}
finalRows = append(finalRows, masterData)
}
z, err := json.Marshal(finalRows)