Execute and assign the result of a SQL query

[adriendumont]

Hey there!

I'm currently migrating a project from go-pg to bun and I'm having a bit of difficulties migrating the following SQL query:

query := `
		SELECT agg_scores
		FROM (
			SELECT jsonb_agg(score) AS agg_scores
			FROM (
				SELECT *
				FROM (
					SELECT jsonb_array_elements(scores)
					FROM recommendations
					WHERE user_id = ?
				) a(score)
				ORDER BY (score->>'recommendation_score')::float DESC
			) sub
		) agg
		WHERE agg_scores IS NOT NULL
	`

with go-pg I'd have something like _, err := db.QueryOne(pg.Scan(&recommendations), query, userId) and the data would be assigned to a zero length slice of types:

type Recommendation struct {
	ID                  int     `json:"id"`
	Name                string  `json:"name"`
	RecommendationScore float64 `json:"recommendation_score"`
}

With bun, I can't figure out how to make Scan properly assign the values. This is the code:

rows, err := db.Query(query, userId)
	if err == nil {
		rows.Scan(&recommendations)
	}

Now, I'm also trying with QueryRow (this particular query should return either no rows or only 1 row but in this case I'm getting the following error:

sql: Scan error on column index 0, name "agg_scores": unsupported Scan, storing driver.Value type []uint8 into type *[]Recommendation

So, my question would be how do i go about implementing a similar thing as in go-pg? Is it possible out of the box?

[vmihailenco]

I believe it should be:

rows, err := db.Query(query, userId)
if err == nil {
	db.ScanRows(&recommendations)
}

But we probably should also support something like this:

err := db.Raw(query, args).Scan(ctx, &dest)

[adriendumont]

Thanks so much for the reply!

ScanRows doesn't assign the query result either. It just creates an empty entry in the recommendations slice(id: 0, name: '' ...)
If it helps, the db query result is something like this:

[
 {"id": 42, "name": "the_guide", "recommendation_score": 999},
 {"id": 33, "name": "harry", "recommendation_score": 998},
 ...
]

I could not find a definition for Raw but i see that there's an Exec, however it does not return any rows.

[vmihailenco]

Let's check if we make any progress in #596