如何将jsonb列扫描到结构/指针切片？

Question

I have a Postgresql jsonb column I am trying to retrieve in my app. I am able to retrieve a plain old struct/pointer from a jsonb column but am unable to retrieve a slice of structs/pointers. I have:

package main

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

    _ "github.com/lib/pq"
)

// Person is an employee
type Person struct {
    Name     string
    Children []*Child
    Job
}

// Child is a child of an employee
type Child struct {
    Name string
    // other fields
}

// Job is the employment of a person
type Job struct {
    Position string
    // other fields
}

func main() {
    db, err := sql.Open("postgres",
        fmt.Sprintf(
            "user=%s password=%s host=%s database=%s sslmode=require",
            os.Getenv("user"), os.Getenv("pword"), os.Getenv("h"), os.Getenv("db"),
        ),
    )
    if err != nil {
        panic(err)
    }

    defer db.Close()
    db.SetMaxIdleConns(0)

    // create table
    if _, err = db.Exec("DROP table mytable"); err != nil {
        fmt.Printf("cannot delete table %v", err)
    }

    if _, err = db.Exec("CREATE TABLE mytable (name text, children jsonb, job jsonb)"); err != nil {
        fmt.Printf("cannot create table %v", err)
    }

    // insert some rows
    for _, person := range []Person{
        Person{"Bob", []*Child{&Child{"Fred"}, &Child{"Mary"}}, Job{"welder"}},
        Person{"Jane", []*Child{&Child{"Ben"}, &Child{"Emily"}}, Job{"machinist"}},
    } {
        c, e := json.Marshal(person.Children)
        if e != nil {
            fmt.Printf("cannot marshal children %v", err)
        }

        j, e := json.Marshal(person.Job)
        if e != nil {
            fmt.Printf("cannot marshal job %v", err)
        }

        if _, err = db.Exec("INSERT INTO mytable (name, children, job) VALUES ($1,$2,$3)", person.Name, string(c), string(j)); err != nil {
            fmt.Printf("cannot insert value %v", err)
        }
    }

    //selectJob(db)
    selectChildrenAndJob(db)

}

func selectJob(db *sql.DB) {
    p := &Person{}

    err := db.QueryRow("SELECT job FROM mytable LIMIT 1").Scan(&p.Job)
    switch {
    case err == sql.ErrNoRows:
        fmt.Println("No rows.")
    case err != nil:
        fmt.Println("cannot retrieve rows", err)
    default:
        fmt.Printf("job %v
", p.Job)
    }
}

func selectChildrenAndJob(db *sql.DB) {
    p := &Person{}

    err := db.QueryRow("SELECT children, job FROM mytable LIMIT 1").Scan(&p.Children, &p.Job)
    switch {
    case err == sql.ErrNoRows:
        fmt.Println("No rows.")
    case err != nil:
        fmt.Println("cannot retrieve rows", err)
    default:
        fmt.Printf("children %v; job %v
", p.Children, p.Job)
    }
}

// Scan scans for Child
func (c *Child) Scan(value interface{}) error {
    return json.Unmarshal(value.([]byte), c)
}

// Scan scans for Job
func (j *Job) Scan(value interface{}) error {
    return json.Unmarshal(value.([]byte), j)
}

The error I get:

Scan error on column index 0: unsupported Scan, storing driver.Value type []uint8 into type *[]*main.Child

If I uncomment "selectJob(db)" and run that it works. So I cannot figure out how to Scan into a slice of structs/pointers. Any suggestions?

Answer 1

You skipped the JSON decoding step.

You'll be getting the jsonb field back from postgres as a string. Then you'll have to json.Unmarshal it into &p.Children:

func selectChildrenAndJob(db *sql.DB) {
    p := &Person{}
    var children string
    err := db.QueryRow("SELECT children, job FROM mytable LIMIT 1").Scan(&children, &p.Job)
    switch {
    case err == sql.ErrNoRows:
        fmt.Println("No rows.")
    case err != nil:
        fmt.Println("cannot retrieve child + job rows", err)
    default:
        err = json.Unmarshal([]byte(children), &p.Children)
        if err != nil {
            fmt.Printf("Failed to unmarshal children: %s
", err)
            return
        }
        childlist := make([]Child, 0)
        for _, c := range p.Children {
            childlist = append(childlist, *c)
        }
        fmt.Printf("children %v; job %v
", childlist, p.Job)
    }
}

You would have noticed this on your other fields as well if they hadn't been string types.

You can think of it as reversing the steps you took when you were inserting data:

c, e := json.Marshal(person.Children)
... "VALUES ($1,$2,$3)", person.Name, string(c), // ...

childlist and the corresponding loop at the end are just to satisfy your print format and print the values instead of the pointers. If you don't mind printing pointers instead, another alternative is to skip that and just

fmt.Printf("Person with children and job: %v
", p)