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
– mkopriva, Commented Apr 8, 2021 at 8:37
... or use OrganizationsIds pq.StringArray and then in scan: row.Scan(..., &channel.OrganizationIds). — mkopriva
– mkopriva, Commented Apr 8, 2021 at 8: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
– Nurzhan Nogerbek, Commented Apr 8, 2021 at 8: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 function, e.g. have OrganizationsIds []*string and scan like this row.Scan(..., pq.Array(&channel.OrganizationIds)). — mkopriva
– mkopriva, Commented Apr 8, 2021 at 8: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
– Nurzhan Nogerbek, Commented Apr 8, 2021 at 8:57

Nurzhan Nogerbek · Accepted Answer · 2021-04-08 10:16:05Z

1

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

answered Apr 8, 2021 at 10:16

Nurzhan Nogerbek

5,27621 gold badges102 silver badges203 bronze badges

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

Comments

Shubham Jain · Accepted Answer · 2023-10-11 12:59:22Z

1

Another way to solve this:

Create a slice:

var userIds []string{}

Use array_remove and to_json functions along with array_agg, like this:

TO_JSON(ARRAY_REMOVE(ARRAY_AGG(table."column"), NULL)) AS "userIds"

Simply scan results into slice, like this :

err := rows.Scan(&userIds)

answered Oct 11, 2023 at 12:59

Shubham Jain

1,0361 gold badge11 silver badges18 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related