Function in Go to execute select query on database and return json output

I am writing a function in Go to execute select query on database.

Input: String e.g. "Select id, name, age from sometable"
This query changes everytime.

Output: Output of select query in json format.

Sample Expected output: {"Data":[{"id":1,"name":"abc", "age":40},{"id":2,"name":"xyz", "age":45}]}

Sample Actual output: {"Data":[[1,"abc",40],[2,"xyz",45]]}

Instead of i.e. column_name:value, I get only values.
How do I get the expected output?

func executeSQL(queryStr string) []byte {
connString := createConnectString()
conn, err := sql.Open("mssql", connString)
if err != nil {
	log.Fatal("Error while opening database connection:", err.Error())
}
defer conn.Close()

rows, err := conn.Query(queryStr)
if err != nil {
	log.Fatal("Query failed:", err.Error())
}
defer rows.Close()

columns, _ := rows.Columns()
count := len(columns)

var v struct {
	Data []interface{} // `json:"data"`
}

for rows.Next() {
	values := make([]interface{}, count)
	valuePtrs := make([]interface{}, count)
	for i, _ := range columns {
		valuePtrs[i] = &values[i]
	}
	if err := rows.Scan(valuePtrs...); err != nil {
		log.Fatal(err)
	}
	v.Data = append(v.Data, values)
}
jsonMsg, err := json.Marshal(v)
return jsonMsg
}

Got the solution. Here is what I did.

func executeSQL(queryStr string) []byte {
connString := createConnectString()
conn, err := sql.Open("mssql", connString)
if err != nil {
	log.Fatal("Error while opening database connection:", err.Error())
}
defer conn.Close()

rows, err := conn.Query(queryStr)
if err != nil {
	log.Fatal("Query failed:", err.Error())
}
defer rows.Close()

columns, _ := rows.Columns()
count := len(columns)

var v struct {
	Data []interface{} // `json:"data"`
}

for rows.Next() {
	values := make([]interface{}, count)
	valuePtrs := make([]interface{}, count)
	for i, _ := range columns {
		valuePtrs[i] = &values[i]
	}
	if err := rows.Scan(valuePtrs...); err != nil {
		log.Fatal(err)
	}
	
//Created a map to handle the issue
	var m map[string]interface{}
	m = make(map[string]interface{})
	for i := range columns {
		m[columns[i]] = values[i]
	}
	v.Data = append(v.Data, m)
}
jsonMsg, err := json.Marshal(v)
return jsonMsg
}

Let me know if there exists a better solution.

This code is directly from my "sandbox" for MsSQL (using denisenkom/go-mssqldb and jmoiron/sqlx) - I think it helps showing different approaches and probably QueryIntoMap is what you were looking for:

package main
import (
"log"
"fmt"
_ "github.com/denisenkom/go-mssqldb"
"time"
"github.com/jmoiron/sqlx"
"encoding/json"
)
type Customer struct {
CustomerId string `db:"customerID" json:"customer_id"`
Company    interface{} `db:"companyName" json:"company_name"`
Contact    interface{} `db:"contactName" json:"contact_name"`
}
func main() {
connection := "server=192.168.55.3\\SqlExpress2012;database=Northwind;user id=me;Password=secret"
//QueryIntoMap(connection)
ScanIntoSlice(connection)
}
func QueryIntoMap(connection string) {
fmt.Println("QueryIntoMap sample")
fmt.Println("--------------------")
sel := `select customerId, companyName, contactName
from customers
where customerId = :id`
values := make(map[string]interface{})
db, err := sqlx.Open("mssql", connection)
//db.MapperFunc(strings.ToUpper)
e(err)
defer db.Close()
tx := db.MustBegin()
stmt, err := tx.PrepareNamed(sel)
e(err)
stmt.QueryRowx(map[string]interface{}{"id": "BONAP"}).MapScan(values)
tx.Commit()
for k, v := range values {
fmt.Printf("%s %v\n", k, v)
}
js, err := json.Marshal(values)
if err != nil {
fmt.Println(err)
}
fmt.Println(string(js))
fmt.Println("--------------------")
}
func ScanIntoStruct(connection string) {
fmt.Println("Scan into struct sample")
fmt.Println("--------------------")
db, err := sqlx.Open("mssql", connection)
e(err)
defer db.Close()
customer := Customer{}
rows, err := db.Queryx(`select customerID, companyName, contactName
from Customers`)
for rows.Next() {
err = rows.StructScan(&customer)
if err != nil {
log.Fatalln(err)
}
//fmt.Printf("%#v\n", user)
fmt.Printf("%-10s %-50v %-50v\n",
customer.CustomerId,
customer.Company,
customer.Contact)
js, err := json.Marshal(customer)
e(err)
fmt.Println(string(js))
}
fmt.Println("--------------------")
}
func ScanIntoSlice(connection string) {
fmt.Println("Scan into slice sample")
fmt.Println("--------------------")
start := time.Now()
db, err := sqlx.Open("mssql", connection)
e(err)
defer db.Close()
customers := []Customer{}
err = db.Select(&customers, `select customerID, companyName, contactName from customers`)
e(err)
for i, customer := range customers {
fmt.Printf("%3d. %-10s %-50v %-50v\n",
i,
customer.CustomerId,
customer.Company,
customer.Contact)
}
js, err := json.Marshal(customers)
e(err)
fmt.Println(string(js))
fmt.Printf("%s", time.Since(start))
fmt.Println("--------------------")
}
func e(err error) {
if err != nil {
log.Fatal(err)
}
}