使用 jsonb_agg/jsonb_build_object 解析为内部结构

Question

每当我尝试获取（选择/扫描）组（外部结构）及其协作者（内部结构）时，我都会收到以下错误：// sql: Scan error on column index ..., name "collaborators": unsupported Scan, storing driver.Value type []uint8 into type *[]User

我正在使用sqlx（带有pgx驱动程序）。

从 db 获取的代码是：

func (psql *Postgres) GetGroups(someParam string) ([]Group, error) {

   groups := []Group{}

   err := psql.db.Unsafe().Select(&groups, <the query ...>, someParam)

   ....

}

type Postgres struct {

    db      *sqlx.DB

    config  *config.PostgresDB

    timeout time.Duration

}

这是 SQL 查询：

SELECT groups.id, 

       groups.title,

       JSONB_AGG(JSONB_BUILD_OBJECT(

        'id', u.id,

        'first_name', u.first_name, 

        'last_name', u.last_name,

        'user_pic_url', u.user_pic_url)) as collaborators

FROM groups

JOIN user_group_permissions p

ON   p.group_id = groups.id

JOIN users u

ON   u.id = p.user_id

这些是结构：

type Group struct {

    Id             string  `json:"id" db:"id"`

    Title          string  `json:"title"   db:"title"`

    Collaborators  []User  `json:"collaborators" db:"collaborators"`

}

type User struct {

    Id            string  `json:"id" db:"id"`

    FirstName     string  `json:"first_name" db:"first_name"`

    LastName      string  `json:"last_name" db:"last_name"`

    ProfilePhoto  *string `json:"profile_photo" db:"user_pic_url"`

}

我有一个简单的组表，一个用户表和表，它代表对组有权限的所有用户：

CREATE TABLE groups (

   id    int UNIQUE NOT NULL generated always as identity,

   title text

)

CREATE TABLE users (

    id       bigint UNIQUE NOT NULL generated always as identity,

    first_name   text NOT NULL,

    last_name    text NOT NULL,

    user_pic_url text

)

CREATE TABLE user_group_permissions (

   group_id   unsigned_int,

   user_id    unsigned_bigint,

   permission unsigned_smallint,

)

CREATE DOMAIN unsigned_smallint AS smallint

   CHECK(VALUE >= 0 AND VALUE < 32767);

CREATE DOMAIN unsigned_int AS int

   CHECK(VALUE >= 0 AND VALUE < 2147483647);

CREATE DOMAIN unsigned_bigint AS bigint

   CHECK(VALUE >= 0 AND VALUE < 9223372036854775807);

Answer 1

import "encoding/json"type Group struct {&nbsp; &nbsp; Id&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;string&nbsp; &nbsp;`json:"id" db:"id"`&nbsp; &nbsp; Title&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; string&nbsp; &nbsp;`json:"title"&nbsp; &nbsp;db:"title"`&nbsp; &nbsp; Collaborators&nbsp; UserList `json:"collaborators" db:"collaborators"`}type User struct {&nbsp; &nbsp; Id&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; string&nbsp; `json:"id" db:"id"`&nbsp; &nbsp; FirstName&nbsp; &nbsp; &nbsp;string&nbsp; `json:"first_name" db:"first_name"`&nbsp; &nbsp; LastName&nbsp; &nbsp; &nbsp; string&nbsp; `json:"last_name" db:"last_name"`&nbsp; &nbsp; ProfilePhoto&nbsp; *string `json:"profile_photo" db:"user_pic_url"`}type UserList []Userfunc (list *UserList) Scan(src interface{}) error {&nbsp; &nbsp; var data []byte&nbsp; &nbsp; switch v := src.(type) {&nbsp; &nbsp; case []byte:&nbsp; &nbsp; &nbsp; &nbsp; data = v&nbsp; &nbsp; case string:&nbsp; &nbsp; &nbsp; &nbsp; data = []byte(v)&nbsp; &nbsp; default:&nbsp; &nbsp; &nbsp; &nbsp; return nil // or return some error&nbsp; &nbsp; }&nbsp; &nbsp; return json.Unmarshal(data, list)}