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

Question

我有一个Postgresql jsonb列，我想在我的应用程序中检索。我能够从jsonb列中检索一个普通的旧结构/指针，但无法检索结构/指针的切片。我有：

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

我得到的错误：

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

如果我取消注释“ selectJob（db）”并运行它就可以了。因此，我无法弄清楚如何扫描到结构/指针的一部分。有什么建议？

Answer 1

您跳过了JSON解码步骤。

您将以字符串形式从postgres获取jsonb字段。然后，您必须将json.Unmarshal其放入&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\n", err)
            return
        }
        childlist := make([]Child, 0)
        for _, c := range p.Children {
            childlist = append(childlist, *c)
        }
        fmt.Printf("children %v; job %v\n", childlist, p.Job)
    }
}

如果其他字段不是字符串类型，您也会在其他字段上注意到这一点。

您可以认为它与您插入数据时所采取的步骤相反：

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

childlist和最后的相应循环只是为了满足您的打印格式并打印值而不是指针。如果您不介意打印指针，另一种选择是跳过该指针

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