sqlx scan postgres array into struct

Question

I'm trying to create a basic commenting api in go. I can't seem to figure out how to scan postgresql arrays into an array of structs within a struct. I think I could probably have Thread.Posts type be jsonb but that seems inelegant since I would have to unmarshall it I think.

sql: Scan error on column index 3, name "posts": unsupported Scan, storing driver.Value type []uint8 into type *[]models.Post

var threadSchema = `
CREATE TABLE IF NOT EXISTS thread (
  id         SERIAL PRIMARY KEY,
  name       VARCHAR(100) NOT NULL,
  profile_id INTEGER REFERENCES profile (id)
)`

var postSchema = `
CREATE TABLE IF NOT EXISTS post (
  id         SERIAL PRIMARY KEY,
  comment    TEXT,
  profile_id INTEGER REFERENCES profile (id),
  thread_id  INTEGER REFERENCES thread (id)
)`

type Post struct {
    Id        int    `db:"id" json:"id"`
    Comment   string `db:"comment" json:"comment" binding:"required" form:"comment"`
    ProfileId int   `db:"profile_id" json:"profile_id" binding:"required" form:"profile_id"`
    ThreadId  int    `db:"thread_id" json:"thread_id" binding:"required" form:"thread_id"`
}

type Thread struct {
    Id        int    `db:"id" json:"id"`
    Name      string `db:"name" json:"name" binding:"required" form:"name"`
    ProfileId int    `db:"profile_id" json:"profile_id" binding:"required" form:"profile_id"`
    Posts     []Post `db:"posts" json:"posts" form:"posts"`
}

func GetThreads(db *sqlx.DB, c *gin.Context) {
    threads := []Thread{}
    err := db.Select(&threads, `
    SELECT thread.id,thread.name,thread.profile_id,array_agg(post.id) AS posts
    FROM thread
    INNER JOIN post ON thread.id = post.thread_id
    GROUP BY thread.id;
  `)
    if err != nil {
        log.Fatal(err)
    }
    c.JSON(http.StatusOK, gin.H{"data": threads})
}

Answer 1

You could define your type:

type Posts []Post

// Scan implements the sql.Scanner interface.
func (a *Posts) Scan(src interface{}) error {
  // ...
}

// Value implements the driver.Valuer interface.
func (a Posts) Value() (driver.Value, error) {
  // ...
}

For more information on the implementation see eg here

Answer 2

First off, you can't do this with sqlx, whether or not you're using Postgres arrays.

Second, your SQL query is simply aggregating Post IDs, not the content of the posts, so there's no way to get the data you want (using Go or otherwise).

So here's what you can do:

1. Use an anonymous embedded struct, capture all of the Post content in your SQL query, and then merge your duplicated Threads.

   type Post struct {
       Id        int    `db:"id" json:"id"`
       Comment   string `db:"comment" json:"comment" binding:"required" form:"comment"`
       ProfileId int   `db:"profile_id" json:"profile_id" binding:"required" form:"profile_id"`
       ThreadId  int    `db:"thread_id" json:"thread_id" binding:"required" form:"thread_id"`
   }
   
   type ThreadDb struct {
       Id        int    `db:"id" json:"id"`
       Name      string `db:"name" json:"name" binding:"required" form:"name"`
       ProfileId int    `db:"profile_id" json:"profile_id" binding:"required" form:"profile_id"`
       Post
   }
   
   type Thread struct {
       Id        int    `db:"id" json:"id"`
       Name      string `db:"name" json:"name" binding:"required" form:"name"`
       ProfileId int    `db:"profile_id" json:"profile_id" binding:"required" form:"profile_id"`
       Posts     []Post `db:"posts" json:"posts" form:"posts"`
   }
   
   
   func GetThreads(db *sqlx.DB, c *gin.Context) {
       threads := []ThreadDb{}
       err := db.Select(&threads, `
       SELECT thread.id,thread.name,thread.profile_id,post.id,post.comment,post.profile_id,post.thread_id
       FROM thread
       INNER JOIN post ON thread.id = post.thread_id
       GROUP BY post.id;
     `)
   
       thread_map := make(map[string]Thread)
   
       for i, thread := range threads {
           if _, ok := thread_map[thread.Id]; ok {
               thread_map[thread.Id].Posts = append(thread_map[thread.Id].Posts, thread.Post)
           } else {
               thread_map[thread.Id] = Thread{thread.Id, thread.Name, thread.ProfileId, []Post{thread.Post}}
           }
       }
   
       var threadSlice []string
   
       for k := range thread_map {
           threadSlice = append(threadSlice, k)
       }
   
       if err != nil {
           log.Fatal(err)
       }
       c.JSON(http.StatusOK, gin.H{"data": threadSlice})
   }
   

2. Use GROUP_CONCAT or similar. I wouldn't recommend unless you plan on having a maximum of about 100 posts per thread.