database/sql/rows to json...

output.txt

eve-macbook:~ go run rowstojson.go
[
	{
		"id": 0,
		"name": "Peck Duran",
		"street": "Chapel Street",
		"city": "Orbin",
		"zip": "94999",
		"state": "Maine",
		"email": "[email protected]",
		"phone": "+1 (859) 111-1111",
		"friends": [
			{
				"id": 0,
				"name": "Hendricks Vaughan"
			},
			{
				"id": 10,
				"name": " Yesenia Franks "
			},
			{
				"id": 13,
				"name": " Wilder Landry "
			}
		]
	},
	{
		"id": 2,
		"name": " Vilma Barrera",
		"street": "Bayview Place",
		"city": "Fleetmix",
		"zip": "94998",
		"state": "Washington",
		"email": "[email protected]",
		"phone": "+1 (884) 222-2222",
		"friends": [
			{
				"id": 3,
				"name": " Tisha Kline"
			},
			{
				"id": 2,
				"name": " Mccarthy Moreno"
			},
			{
				"id": 4,
				"name": " Neva Pennington"
			}
		]
	}
]

rowstojson.go

package main

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"log"

	_ "github.com/lib/pq"
)

type Friend struct {
	Id   int64  `json:"id"`
	Name string `json:"name"`
}

type Message struct {
	Id      int64    `json:"id"`
	Name    string   `json:"name"`
	Street  string   `json:"street"`
	City    string   `json:"city"`
	Zip     string   `json:"zip"`
	State   string   `json:"state"`
	Email   string   `json:"email"`
	Phone   string   `json:"phone"`
	Friends []Friend `json:"friends"`
}

func main() {
	db, err := sql.Open("postgres", "user=json password=password dbname=json sslmode=disable")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	_, err = db.Exec("create table friends (id integer, name varchar(100), street varchar(1000), city varchar(100), zip varchar(100), state varchar(100), email varchar(100), phone varchar(100), friend_id integer, friend_name varchar(100))")
	if err != nil {
		log.Println("error while creating table:", err)
	}

	stmt, err := db.Prepare("insert into friends values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)")
	if err != nil {
		log.Println("error while creating statment:", err)
	}

	_, err = stmt.Exec(0, "Peck Duran", "Chapel Street", "Orbin", "94999", "Maine", "[email protected]", "+1 (859) 111-1111", 0, "Hendricks Vaughan")
	if err != nil {
		log.Println("error while inserting:", err)
	}
	_, err = stmt.Exec(0, "Peck Duran", "Chapel Street", "Orbin", "94999", "Maine", "[email protected]", "+1 (859) 111-1111", 10, " Yesenia Franks ")
	if err != nil {
		log.Println("error while inserting:", err)
	}
	_, err = stmt.Exec(0, "Peck Duran", "Chapel Street", "Orbin", "94999", "Maine", "[email protected]", "+1 (859) 111-1111", 13, " Wilder Landry ")
	if err != nil {
		log.Println("error while inserting:", err)
	}
	_, err = stmt.Exec(2, " Vilma Barrera", "Bayview Place", "Fleetmix", "94998", "Washington", "[email protected]", "+1 (884) 222-2222", 3, " Tisha Kline")
	if err != nil {
		log.Println("error while inserting:", err)
	}
	_, err = stmt.Exec(2, " Vilma Barrera", "Bayview Place", "Fleetmix", "94998", "Washington", "[email protected]", "+1 (884) 222-2222", 2, " Mccarthy Moreno")
	if err != nil {
		log.Println("error while inserting:", err)
	}
	_, err = stmt.Exec(2, " Vilma Barrera", "Bayview Place", "Fleetmix", "94998", "Washington", "[email protected]", "+1 (884) 222-2222", 4, " Neva Pennington")
	if err != nil {
		log.Println("error while inserting:", err)
	}
	stmt.Close()

	rows, err := db.Query("select * from friends")
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()

	msgs := []Message{}
	for rows.Next() {
		msg := Message{}
		friend := Friend{}
		err := rows.Scan(&msg.Id, &msg.Name, &msg.Street, &msg.City, &msg.Zip, &msg.State, &msg.Email, &msg.Phone, &friend.Id, &friend.Name)
		if err != nil {
			log.Fatal(err)
		}
		if len(msgs) == 0 || msgs[len(msgs)-1].Id != msg.Id {
			msg.Friends = append(msg.Friends, friend)
			msgs = append(msgs, msg)
		} else {
			msgs[len(msgs)-1].Friends = append(msgs[len(msgs)-1].Friends, friend)
		}
	}

	// don't do it this way for real, but this is one way to get JSON out
	buf, err := json.MarshalIndent(msgs, "", "\t")
	if err != nil {
		log.Fatal(err)
	}
	fmt.Println(string(buf))
}

how about dynamic columns ?