Convert Select query obtained from Postgre with Go to JSON

Thing you want to do

--I want to convert the entire query obtained by the SELECT statement to JSON --It seems that the scan of the sql package is processing line by line, so you need to create an array in the loop (?) --Convert from array with json package --The reference code is on the MS site

code

main.go

package main

import (
    "database/sql"
    "encoding/json"
    "fmt"
    _ "github.com/lib/pq"
)

const (
    // Initialize connection constants.
    HOST     = "127.0.0.1"
    DATABASE = "testdb"
    USER     = "root"
    PASSWORD = "password"
)

func checkError(err error) {
    if err != nil {
        panic(err)
    }
}

func main() {

    // Initialize connection string.
    var connectionString string = fmt.Sprintf("host=%s user=%s password=%s dbname=%s sslmode=disable", HOST, USER, PASSWORD, DATABASE)

    // Initialize connection object.
    db, err := sql.Open("postgres", connectionString)
    checkError(err)

    err = db.Ping()
    checkError(err)
    fmt.Println("Successfully created connection to database")

    // Read rows from table.
    var id int
    var name string
    var quantity int

    sql_statement := "SELECT * from inventory;"
    rows, err := db.Query(sql_statement)
    checkError(err)
    defer rows.Close()

    //Define structure
    type data struct {
        ID int
        Name string
        Quantity int
    }

    //Declare array type
    var fruit []data

    for rows.Next() {
        switch err := rows.Scan(&id, &name, &quantity); err {
        case sql.ErrNoRows:
            fmt.Println("No rows were returned")
        case nil:
            fmt.Printf("Data row = (%d, %s, %d)\n", id, name, quantity)

            //Add array line by line
            fruit = append(fruit,data{
                ID: id,
                Name: name,
                Quantity: quantity,
            })
        default:
            checkError(err)
        }
    }

    fruitjson, _ := json.Marshal(fruit)
    fmt.Println(string(fruitjson))

}

result

yuta:~/postgre $ go run main.go 
Successfully created connection to database
Data row = (1, banana, 150)
Data row = (2, orange, 154)
Data row = (3, apple, 100)
Data row = (4, test, 100)
[{"ID":1,"Name":"banana","Quantity":150},{"ID":2,"Name":"orange","Quantity":154},{"ID":3,"Name":"apple","Quantity":100},{"ID":4,"Name":"test","Quantity":100}]

reference

https://docs.microsoft.com/ja-jp/azure/postgresql/connect-go