从SQL结果为API创建JSON

Question

I'm very new to Go so hopefully this is simple to fix. I want to build a JSON value from rows in an SQL database. It doesn't have to be SQL but I'm looking for a way to generate a list of structs or a container that holds many values in order to generate JSON from it.

For example, an HTTP GET request to "/post" would return:

{
  "posts": [
    { "title": "First title", "created_by": "User1" },
    { "title": "My second post", "created_by": "User1"}
  ]
}

I don't know what's the proper way to do this. I've tried other ways using an array of pointers but it doesn't come out correctly. So far it prints out the correct values for each struct but the resulting Marshal is empty [{},{},{}]

Please go easy on me :)

example.go

package main

import (
    "fmt"
    "encoding/json"
    "time"
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
)


type Post struct {
    name string `json:"name"`
    created_by string `json:"created_by"`
    created_at time.Time `json:"created_at"`
}

type Posts []Post

func main() {

    db, err := sql.Open("mysql", "root:1234@/blog?parseTime=true")
    if err != nil { panic(err.Error()) }
    defer db.Close()

    err = db.Ping()
    if err != nil { panic(err.Error()) }

    rows, err := db.Query("select title, created_by, created_at from post")
    if err != nil { panic(err.Error()) }
    defer rows.Close()

    var posts Posts
    for rows.Next() {            
        var post Post
        err := rows.Scan(&post.name, &post.created_by, &post.created_at)
        if err != nil { panic(err.Error()) }
        fmt.Printf("name=%s, created_by=%s, created_at=%s
", post.name, post.created_by, post.created_at)
        posts = append(posts, post)                  
    }    

    data, err := json.Marshal(posts)    
    if err != nil { panic(err.Error()) }
    fmt.Printf("%s
", data)
}

Answer 1

Your fields aren't exported, so the json package can't see them.

See 50 Shades of Go for a longer explanation and others common gotchas.