我有一个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)”并运行它就可以了。因此,我无法弄清楚如何扫描到结构/指针的一部分。有什么建议?
您跳过了JSON解码步骤。
您将以字符串形式从postgres获取jsonb字段。然后,您必须将json.Unmarshal其放入&p.Children:
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和最后的相应循环只是为了满足您的打印格式并打印值而不是指针。如果您不介意打印指针,另一种选择是跳过该指针
childlist
fmt.Printf("Person with children and job: %v\n", p)