How to format sql output into json

I have a Go function that queries a database and returns two columns:

var colA string
var colB string

	err = db.QueryRow("select colA, colB from table where colA = %v", 1).Scan(&colA,&colB)
	if err != nil {
		fmt.Printf(err.Error())
	}
	fmt.Println(colA,colB)

	return nil

I would like to return the output in json format like {colA:colB}. I have played around with the json.Marshal function but can't get it working correctly.

You could make a struct if you know what your data is going to be: e.g

type User struct {
    Username string `json:"username"`
    Email    string `json:"email"`
}

Then on your query:

user := User{}
err = db.QueryRow("select colA, colB from table where colA = %v", 1).Scan(&user.Username,&user.Email)
    if err != nil {
        fmt.Printf(err.Error())
}

Then call marshal on it

msg, err := json.Marshal(user)
if err != nil {
    log.Println(err)
}

fmt.Println(string(msg)) // "{ "username": "Blah", "email": "asd@asd.com" }"

Also with the json.Marshal package if you're calling Marshal on a struct your fields MUST be exported by using a capital first letter on the field name, e.g. Username

> // Field is ignored by this package. Field int json:"-"
>
> // Field appears in JSON as key "myName". Field int json:"myName"
>
> // Field appears in JSON as key "myName" and // the field is omitted
> from the object if its value is empty, // as defined above. Field int
> json:"myName,omitempty"
>
> // Field appears in JSON as key "Field" (the default), but // the
> field is skipped if empty. // Note the leading comma. Field int
> json:",omitempty"

https://golang.org/pkg/encoding/json/#Marshal

You can create a one element map and marshal it:

m := map[string]string{colA: colB}
b, err := json.Marshal(m)

Full example: https://play.golang.org/p/ntT5h3oHvY

I'd suggest looking into some database management frameworks.

I personally use gorm (github.com/jinzhu/gorm) for my database needs. It has functionality for automatically creating your database using structs and parsing them back out again.

You can pair that with the "encoding/json" package to take it to/from json to/from your database.

Here's some personal code you can use for reference:

Struct:

type Application struct {
  Id       		int64 			`json:"id"`
  UserID    	int64			`sql:"not null;" json:"user_id"`
  Name	 		string			`sql:"size:255; not null; unique;" json:"name"`
  ExposedPorts	string			`json:"exposed_ports"` //docker
  DockerImage	string			`sql:"size:255; not null;" json:"docker_image"`
  Dependencies 	string 			`json:"dependencies"`  
  IsEnabled		bool 			`sql:"default:true" json:"is_enabled"`

}

JSON:

func (a *Application) GetJSON() (string, error) {
	b, err := json.Marshal(a)
    if err != nil {
        logging.Log(err)
        return "",err;
    }
    return string(b),err;
}

Database

    //Get application information
func GetApplication(id int64) (*models.Application, error) {
 app := &models.Application{}

 err := db.Where(&models.Application{Id: id}).First(&app).Error

 return app, err
}

func GetApplications() ([]models.Application, error) {
    //Returns a list of all applications 
    apps := []models.Application{}

    err := db.Find(&apps).Error

    return apps, err
}

//delete application from database
func DeleteApplication(id int64) (bool, error) {
    logging.Log("Deleting Application: ", id)

    app := models.Application{}

    err := db.Where(&models.Application{Id: id}).First(&app).Error 

    if err != nil {
        return false, err
    }
    //  TODO: Check for auth
    //      Delete all containers

    //Delete application from database
    err = db.Delete(&app).Error

    if err != nil {
       return false, err
    }

    return true, err

}

//Update Application
func UpdateApplication(app *models.Application) (bool, error) {

    newapp := models.Application{}
    err := db.Where(&models.Application{Id: app.Id}).First(&newapp).Error 

    if err != nil {
       return false, err
    }

    err = db.Save(&app).Error

    if err != nil {
        return false, err
    }

    return true, nil
} 

Hope this helps

Here is a completely sample:

campaign.model.go:

package models

import "database/sql"

// Campaign Model
type Campaign struct {
	CampaignId int `db:"campaign_id" json:"campaignId"`
	CampaignNme string `db:"campaign_nme" json:"campaignNme"`
	OrganizationId sql.NullInt64 `db:"organization_id" json:"organizationId"`
}

campaign.repository.go:

package repositories

import (
	"github.com/jmoiron/sqlx"
	"go-clean-arch/domain/models"
	"go-clean-arch/domain/repositories"
)

type CampaignRepository struct {
	Db *sqlx.DB
}

func NewCampaignRepository(Db *sqlx.DB) repositories.CampaignRepository {
	return &CampaignRepository{Db}
}

func (cr *CampaignRepository)FindById(id string) (models.Campaign, error) {
	campaign := models.Campaign{}
	query := `
		SELECT
			campaign_id,
			campaign_nme,
			organization_id
		FROM "CAMPAIGN" WHERE campaign_id = $1
	`
	err := cr.Db.Get(&campaign, query, id)
	if err != nil {
		return campaign, err
	}
	return campaign, nil
}

main.go:

func main() {
	dbConf := database.PGDatabaseConfig{
		Host: viper.GetString("SQL_HOST"),
		Port: viper.GetString("SQL_PORT"),
		User: viper.GetString("SQL_USER"),
		Password: viper.GetString("SQL_PASSWORD"),
		Dbname: viper.GetString("SQL_DB"),
	}
	db, err := database.ConnectPGDatabase(&dbConf)
	if err != nil {
		log.Fatal(err)
	}

	defer func () {
		if err := recover(); err != nil {
			fmt.Println(err)
		}
	}()

	//userRepository := repositories.NewUserRepository(db)
	//users, _ := userRepository.FindAll()
	//fmt.Printf("%#v", &users)

	campaignRepository := repositories.NewCampaignRepository(db)
	campaign, err := campaignRepository.FindById("102")
	if err != nil {
		panic(err)
	}
	fmt.Printf("%#v", campaign)
	campaignJSON, err := json.Marshal(campaign)
	if err != nil {
		panic(err)
	}
	fmt.Println(string(campaignJSON))

}

Output:

☁  go-clean-arch [master] ⚡  go run main.go
models.Campaign{CampaignId:102, CampaignNme:"Generic Party / Memories", OrganizationId:sql.NullInt64{Int64:0, Valid:false}}
campaignJSON: {"campaignId":102,"campaignNme":"Generic Party / Memories","organizationId":{"Int64":0,"Valid":false}}