Create JSON from SQL result for API

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\n", 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\n", data)
}

I just tried to Marshal a single Post and that doesn't work either. I'm missing something. — Nick
– Nick, Commented Mar 2, 2016 at 17:19

Elwinar · Accepted Answer · 2016-03-02 17:27:39Z

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.

answered Mar 2, 2016 at 17:27

Elwinar

9,5791 gold badge35 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Elwinar Over a year ago

Well, an unexported field is the private equivalent of other languages, so it is absolutely normal another package can't see it. It would defy the definition of private attribute otherwise…

Nick Over a year ago

I was referring to using style (Capitalization) to change behavior rather than a keyword (private like you said) or a symbol (Nim language uses *). Just something to figure out when learning a new language :)

Elwinar Over a year ago

Well… not adding a keyword or symbol to denote private state is shorter to write, faster to read, and in the end simpler. That may be the best example of "Convention over configuration".

Collectives™ on Stack Overflow

Create JSON from SQL result for API

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related