CODEWITHSUNDEEP
sqlpostgresqlgo
Nurzhan Nogerbek
Nurzhan Nogerbek

Reputation: 5246

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

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?

Upvotes: 6

Views: 4457

Answers (2)

Shubham Jain
Shubham Jain

Reputation: 1016

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)

Upvotes: 1

Nurzhan Nogerbek
Nurzhan Nogerbek

Reputation: 5246

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

Upvotes: 1

Related Questions