将一个 PostgreSQL 字段（ARRAY 类型）扫描到一个 Go 结构体切片中。

huangapple
go评论116阅读模式
英文:

Scan a PostgreSQL field (of ARRAY type) into a slice of Go structs

问题

假设我有以下代码：

  1. type User struct {
  2.     ID int64 `json:"id"`
  3.     Posts []Post `json:"posts"`
  4. }
  6. type Post struct {
  7.     ID int64 `json:"id"`
  8.     Text string `json:"text"`
  9. }

SQL查询如下：

  1. WITH temp AS (SELECT u.id AS user_id, p.id AS post_id, p.text AS post_text FROM users u JOIN posts p ON u.id=p.user_id)
  2. SELECT user_id, ARRAY_AGG(ARRAY[post_id::text, post_text]) 
  3. FROM temp
  4. GROUP BY user_id

我想要的是将上述查询的结果扫描到一个User对象的切片中：

  1. import (
  2.     "context"
  3.     "fmt"
  4.     "github.com/jackc/pgx/v4/pgxpool"
  5.     "github.com/lib/pq"
  6. )
  8. var out []User
  10. rows, _ := client.Query(context.Background(), query) // 为简洁起见，省略了错误处理
  12. for rows.Next() {
  13.     var u User
  14.     if err := rows.Scan(&u.ID, pq.Array(&u.Posts)); err != nil {
  15.         return
  16.     }
  17.     out = append(out, u)
  18. }

可以预料到，上述代码会出现以下错误：

pq: cannot convert ARRAY[4][2] to StringArray

这是有道理的，但有没有办法将SQL的输出读取到我的用户切片中呢？

英文:

Let's say I have:

  1. type User struct {
  2.     ID int64 `json:"id"
  3.     Posts []Post `json:"posts"
  4. }
  6. type Post struct {
  7.     ID int64 `json:"id"
  8.     Text string `json:"text"
  9. }

The SQL query:

  1. WITH temp AS (SELECT u.id AS user_id, p.id AS post_id, p.text AS post_text FROM users u JOIN posts p ON u.id=p.user_id)
  3. SELECT user_id, ARRAY_AGG(ARRAY[post_id::text, post_text]) 
  4. FROM temp
  5. GROUP BY user_id
  6. )

What I want is to scan rows from the query above into a slice of User objects:

  1.  import (
  2.     "context"
  3.     "fmt"
  4.     "github.com/jackc/pgx/v4/pgxpool"
  5.     "github.com/lib/pq"
  6.  )
  8.  var out []User
  10.     rows, _ := client.Query(context.Background(), query) // No error handling for brevity
  12.     for rows.Next() {
  14.         var u User
  16.         if err := rows.Scan(&u.ID, pq.Array(&u.Posts)); err != nil {
  17.             return
  18.         }
  20.         out = append(out, u)
  22.     }

Pretty much expectedly, the code above fails with:

pq: cannot convert ARRAY[4][2] to StringArray

This makes sense, but is there a way to read the SQL output into my slice of users?

答案1

得分: 3

lib/pq不支持扫描任意类型的多维数组，例如结构体。如果你想要扫描这样的数组，你需要在自定义的sql.Scanner实现中自己解析和解码它。

例如：

  1. type PostList []Post
  3. func (ls *PostList) Scan(src any) error {
  4.     var data []byte
  5.     switch v := src.(type) {
  6.     case string:
  7.         data = []byte(v)
  8.     case []byte:
  9.         data = v
  10.     }
  12. 	// data变量保存了多维数组的值，类似于：{{"1","foo"}, {"2","bar"}, ...}
  13.     // 上面的示例很容易解析，但过于简单，
  14.     // 数组可能更复杂，因此解析起来可能更困难，
  15.     // 但如果你愿意，这并不是不可能的。
  17.     return nil
  18. }

如果你想了解更多关于PostgreSQL数组表示语法的信息，请参阅：

一种不需要你实现一个解析器来处理PostgreSQL数组的方法是，使用JSON对象而不是PostgreSQL数组来构建并传递给array_agg。这样做的结果将是一个以jsonb为元素类型的一维数组。

  1. SELECT user_id, array_agg(jsonb_build_object('id', post_id, 'text', post_text)) 
  2. FROM temp
  3. GROUP BY user_id

然后，自定义的sql.Scanner实现只需要委托给lib/pq.GenericArray，而另一个特定于元素的sql.Scanner则委托给encoding/json。

  1. type PostList []Post
  3. func (ls *PostList) Scan(src any) error {
  4.     return pq.GenericArray{ls}.Scan(src)
  5. }
  7. func (p *Post) Scan(src any) error {
  8.     var data []byte
  9.     switch v := src.(type) {
  10.     case string:
  11.         data = []byte(v)
  12.     case []byte:
  13.         data = v
  14.     }
  15.     return json.Unmarshal(data, p)
  16. }
  18. type User struct {
  19.     ID    int64    `json:"id"`
  20.     Posts PostList `json:"posts"`
  21. }
英文:

Scanning of multi-dimensional arrays of arbitrary types, like structs, is not supported by lib/pq. If you want to scan such an array you'll have to parse and decode it yourself in a custom sql.Scanner implementation.

For example:

  1. type PostList []Post
  3. func (ls *PostList) Scan(src any) error {
  4.     var data []byte
  5.     switch v := src.(type) {
  6.     case string:
  7.         data = []byte(v)
  8.     case []byte:
  9.         data = v
  10.     }
  12. 	// The data var holds the multi-dimensional array value,
  13.     // something like: {{"1","foo"}, {"2","bar"}, ...}
  14.     // The above example is easy to parse but too simplistic,
  15.     // the array is likely to be more complex and therefore
  16.     // harder to parse, but not at all impossible if that's
  17.     // what you want.
  19.     return nil
  20. }

If you want to learn more about the PostgreSQL array representation syntax, see:

An approach that does not require you to implement a parser for PostgreSQL arrays would be to build and pass JSON objects, instead of PostgreSQL arrays, to array_agg. The result of that would be a one-dimensional array with jsonb as the element type.

  1. SELECT user_id, array_agg(jsonb_build_object('id', post_id, 'text', post_text)) 
  2. FROM temp
  3. GROUP BY user_id

Then the implementation of the custom sql.Scanner just needs to delegate to lib/pq.GenericArray and another, element-specific sql.Scanner, would delegate to encoding/json.

  1. type PostList []Post
  3. func (ls *PostList) Scan(src any) error {
  4.     return pq.GenericArray{ls}.Scan(src)
  5. }
  7. func (p *Post) Scan(src any) error {
  8.     var data []byte
  9.     switch v := src.(type) {
  10.     case string:
  11.         data = []byte(v)
  12.     case []byte:
  13.         data = v
  14.     }
  15.     return json.Unmarshal(data, p)
  16. }
  18. type User struct {
  19.     ID    int64    `json:"id"`
  20.     Posts PostList `json:"posts"`
  21. }

huangapple
  • 本文由 发表于 2022年9月5日 20:07:40
  • 转载请务必保留本文链接：https://go.coder-hub.com/73609123.html
  • go
  • sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定