SQLX scan into array of structs

Question

I'm trying to implement a sql query that returns data in a way that jQuery Datatables expects while allowing users to customize the query (I'm doing alot of input validate to make sure there isn't any sql injection).The query is supposed to filter all of the edits by the object_name and editor display_name. Then of those edits it is supposed to get the last edit for each object based on it's perm_id (uid). Then it tries to fit everything into a jQuery Datatables friendly format that supports pagination. I think the query itself is working I'm just not sure if sqlx supports the array_agg scanning into a struct. When I try to scan it into the DataTableResponse struct I get this error.

unsupported Scan, storing driver.Value type []uint8 into type *[]edits.Edit

type Edit struct {
    Id                 *int            `db:"id" json:"id,omitempty"`
    UserId             *int            `db:"user_id" json:"user_id,omitempty"`
    PermId             *string         `db:"perm_id" json:"perm_id,omitempty"`
    ObjectName         *string        `db:"object_name,omitempty"`
    EditTypeId         *int            `db:"edit_type_id" json:"edit_type_id,omitempty"`
    EditDate           *time.Time      `db:"edit_date" json:"edit_date,omitempty"`

    DisplayName *string `db:"display_name" json:"display_name,omitempty"`
}

type DataTableResponse struct {
    Draw int `db:"draw" json:"draw"`
    RecordsTotal int `db:"records_total" json:"recordsTotal"`
    RecordsFiltered int `db:"records_filtered" json:"recordsFiltered"`
    Data []Edit `db:"data" json:"data"`
}


response := []DataTableResponse{}
err = db.Select(&response,
    fmt.Sprintf(`
        SELECT DISTINCT ON (results.perm_id) %d as draw, array_agg((%s)) as data,COUNT(DISTINCT perm_id) as records_total,COUNT(DISTINCT perm_id) - count(results.*) as records_filtered
        FROM (SELECT edit_metadata.*,user.display_name
                FROM edit_metadata INNER JOIN user 
                ON edit_metadata.user_id = user.id 
                WHERE user.display_name ilike $1 AND edit_metadata.object_name ilike $2 
                ORDER BY edit_metadata.edit_date ASC) 
        as results 
        GROUP BY results.perm_id,results.edit_date
        ORDER BY results.perm_id,%s %s
        LIMIT %s
        OFFSET %s`,draw, requestedFields, sortBy, sortDir,length, start),
    fmt.Sprintf("%%%s%%", c.Query("display_name")),
    fmt.Sprintf("%%%s%%", c.Query("object_name")))
if err != nil {
    log.Fatal(err)
}
c.JSON(http.StatusOK, response)

The query is obviously doing something you aren't expecting. It's returning "data" as a []byte (or possibly a string) which you are trying to have sqlx stuff into a struct. The pieces don't match up. — RayfenWindspear

Eldar Eldar · Accepted Answer · 2020-10-22T06:01:20

I have same issue with array of strings. I found that sqlx scans PostgreSQL's arrays as a string in []uint8

So you need to write your own Scanner/Parser.

SQLX scan into array of structs

1 Answers