Using jsonb_agg/jsonb_build_object to parse to inner structs

Question

Whenever I try to get (select/scan) the Groups (outer struct) along with their Collaborators (inner structs), I'm getting the following error : // sql: Scan error on column index ..., name "collaborators": unsupported Scan, storing driver.Value type []uint8 into type *[]User

I'm using sqlx (with pgx driver).

the code to fetch from db is :

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
}

This is the SQL query :

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

These are the structs :

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"`
}

I have a simple Group table , a User table and table which represents all users with Permissions to the group :

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);

mkopriva · Accepted Answer · 2021-01-15 13:36:42Z

1

import "encoding/json"

type Group struct {
    Id             string   `json:"id" db:"id"`
    Title          string   `json:"title"   db:"title"`
    Collaborators  UserList `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"`
}

type UserList []User

func (list *UserList) Scan(src interface{}) error {
    var data []byte
    switch v := src.(type) {
    case []byte:
        data = v
    case string:
        data = []byte(v)
    default:
        return nil // or return some error
    }
    return json.Unmarshal(data, list)
}

answered Jan 15, 2021 at 13:36

mkopriva

38.7k4 gold badges73 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using jsonb_agg/jsonb_build_object to parse to inner structs

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related