How to properly scan the results of the array_agg function in Golang?

Question

In my Golang (1.15) application I use sqlx package to work with the PostgreSQL database (PostgreSQL 12.5).

My SQL request has an array_agg function that returns the array of strings or null if it's empty.

I am trying to Scan the results of this SQL request but it raises the next error in my program:

sql: Scan error on column index 3, name "organization_ids": unsupported Scan, storing driver.Value type string into type *[]string

Code snippet:

type Channel struct {
    ChannelId           *string   `db:"channel_id" json:"channelId"`
    ChannelName         *string   `db:"channel_name" json:"channelName"`
    OrganizationsIds    *[]string `db:"organizations_ids" json:"organizationsIds"`
}

var channel Channel

row := db.QueryRow(`
    select
        channels.channel_id::text,
        channels.channel_name::text,
        array_agg(distinct channels_organizations_relationship.organization_id)::text[] organizations_ids
    from
        channels
    left join channels_organizations_relationship on
        channels.channel_id = channels_organizations_relationship.channel_id
    where
        channels.channel_id = $1
    group by
        channels.channel_id
    limit 1;`, *channelId)

if err := row.Scan(&channel.ChannelId, &channel.ChannelName, &channel.OrganizationsIds); err != nil {
    fmt.Println(err)
}

return &channel

I also tried to change the data type of the OrganizationsIds field in the Channel struct to *pg.StringArray from the github.com/lib/pq package. In this case, when I Scan, I get the following error in my program:

sql: Scan error on column index 3, name "organizations_ids": unsupported Scan, storing driver.Value type string into type *[]pq.StringArray

My task is to return a list of strings or null/nil to the client for this column.

Can someone explain how to fix this strange behavior?

`pq.StringArray`'s underlying type is a `[]string` so one thing you can do is to convert your field to `pq.StringArray` just before scanning. i.e. have the field be `OrganizationsIds []string`, and then when scanning do `row.Scan(..., (*pq.StringArray)(&channel.OrganizationIds))`. — mkopriva, Apr 08 '21 at 08:37
... or use `OrganizationsIds pq.StringArray` and then in scan: `row.Scan(..., &channel.OrganizationIds)`. — mkopriva, Apr 08 '21 at 08:39
Hello @mkopriva! Thank you for your answer. In fact, I have already checked both options that you recommended. It works only if the column returns the array of strings. When this column is empty and returns null your recommendations raise the error. The error looks like this: `sql: Scan error on column index 3, name "organizations_ids": pq: parsing array element index 0: cannot convert nil to string`. Do you have any ideas on how to fix it? — Nurzhan Nogerbek, Apr 08 '21 at 08:52
That's an individual id inside the array being NULL rather than the whole array, to make that work you would have to use a `[]*string` or `[]sql.NullString` as the target type. Try the [`pq.Array`](https://pkg.go.dev/github.com/lib/pq#Array) function, e.g. have `OrganizationsIds []*string` and scan like this `row.Scan(..., pq.Array(&channel.OrganizationIds))`. — mkopriva, Apr 08 '21 at 08:56
Well, I checked it one more time. My SQL request returns an array with one null value `{NULL}`. It seems like I need to change the request itself. What do you think? — Nurzhan Nogerbek, Apr 08 '21 at 08:57
That depends on whether that's a valid value to return for your program. — mkopriva, Apr 08 '21 at 08:57
I didn't quite understand your last comment. My task is to return a list/array of strings/text or null/nil for this column as the response of my Go function. — Nurzhan Nogerbek, Apr 08 '21 at 09:19
Then you have to make sure that the query doesn't return NULLs inside the array, which it currently does, so yes, you have to fix the SQL first before you fix the scanning. — mkopriva, Apr 08 '21 at 09:20

score 1 · Accepted Answer · answered Apr 08 '21 at 10:16

Well, finally I found the solution of my problem.

I noticed that the array_agg function returns the [null]. I changed the SQL request a little bit to return the null instead of [null]. I make it with this changes:

array_agg(distinct channels_organizations_relationship.organization_id) filter (where channels_organizations_relationship.organization_id is not null)

In this article (Postgres returns [null] instead of [] for array_agg of join table ), you can find many different solutions to this problem.

Then you can use one of this solutions:

The 1 option

We can use OrganizationsIds pq.StringArray in the struct, and then when scanning do this row.Scan(..., &channel.OrganizationIds.

The 2 option

We can use OrganizationsIds []string in the struct, and then when scanning do this row.Scan(..., (*pq.StringArray)(&channel.OrganizationIds)).

How to properly scan the results of the array_agg function in Golang?

1 Answers1