How to format sql output into json

Question

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.

Answer 1

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}}

Answer 2

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 :)

Answer 3

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

Answer 4

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