Database rows scan + unknown number of columns + JSON

Getting Help
2

Is there any simple way to do that?

Not sure it can be called simple, but I got something working.

main.go

package main

import (
	"database/sql"
	"encoding/json"
	"log"
	"os"
	"reflect"

	_ "github.com/mattn/go-sqlite3"
)

func main() {
	log.SetFlags(log.Lshortfile)

	db := initDb()

	b, err := queryToJson(db, "select * from test")
	if err != nil {
		log.Fatalln(err)
	}
	os.Stdout.Write(b)

}

func queryToJson(db *sql.DB, query string, args ...interface{}) ([]byte, error) {
	// an array of JSON objects
	// the map key is the field name
	var objects []map[string]interface{}

	rows, err := db.Query(query, args...)
	if err != nil {
		return nil, err
	}
	for rows.Next() {
		// figure out what columns were returned
		// the column names will be the JSON object field keys
		columns, err := rows.ColumnTypes()
		if err != nil {
			return nil, err
		}

		// Scan needs an array of pointers to the values it is setting
		// This creates the object and sets the values correctly
		values := make([]interface{}, len(columns))
		object := map[string]interface{}{}
		for i, column := range columns {
			object[column.Name()] = reflect.New(column.ScanType()).Interface()
			values[i] = object[column.Name()]
		}

		err = rows.Scan(values...)
		if err != nil {
			return nil, err
		}

		objects = append(objects, object)
	}

	// indent because I want to read the output
	return json.MarshalIndent(objects, "", "\t")
}

func initDb() *sql.DB {
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		log.Fatalln(err)
	}

	_, err = db.Exec(`
create table test (
	id integer primary key,
	field1 integer,
	field2 text
);`)
	if err != nil {
		log.Fatalln(err)
	}

	_, err = db.Exec(`
insert into test (field1, field2) values
(1, 'first'),
(2, 'second')
`)
	if err != nil {
		log.Fatalln(err)
	}

	return db
}

output

$ go run main.go
[
	{
		"field1": 1,
		"field2": "first",
		"id": 1
	},
	{
		"field1": 2,
		"field2": "second",
		"id": 2
	}
]
1 Like