如何将jsonb列扫描到结构/指针切片？ [英] How do I scan a jsonb column to a slice of struct/pointer?

问题描述

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

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\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）并运行它就可以了。因此，我无法弄清楚如何扫描到结构/指针的一部分。有建议吗？

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?

推荐答案

您跳过了JSON解码步骤。

You skipped the JSON decoding step.

您将以字符串形式从postgres获取jsonb字段。然后您必须放入& p.Children ：

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\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)
    }
}

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

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 和最后的相应循环只是为了满足您的打印格式并打印值而不是指针。如果您不介意打印指针，则另一种选择是跳过该指针，而只是

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

这篇关于如何将jsonb列扫描到结构/指针切片？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！