Go SQL map 1:M relation json array to slice

Question

I have a product-image (1:M) relation and basically i want to map those images to a slice in my struct. I am using sqlx library to make it bit easier.

I searched for a while and maybe the best answer was in this thread: Efficiently mapping one-to-many many-to-many database to struct in Golang. The answer with creating the view and returning everything as a json works but it feels somehow hacky.

Ideally what i want to do is use postgres json_agg to return the json array inside the images column that would match my type so i could scan into it.

I did this with nodejs several times but here i don't know how. Do i need to implement custom Scan and Value methods or is there a simpler way. I didn't show this but i also have a category that is 1:1 and i can embed that category in product and do a left join and it works with sqlx, but not with images type.

Simplified models

type Image struct {
  ID int,
  URL string,
  ProductID int
}

type ImageList []*Image

type Product struct {
  ID int `db:"id"`
  Name string `db:"name"`
  Images ImageList `db:"images"`
}

DB Tables

create table product (
  id int generated always as identity primary key,
  name varchar(255),
);

create table product_image (
  id int generated always as identity primary key,
  url text not null,
  product_id int references product(id)
);

I am trying something like this now:

q := `SELECT 
      p.*,
      COALESCE(JSON_AGG(img.*) FILTER (WHERE img.product_id IS NOT NULL), '[]'::json) AS images
      FROM product p
      LEFT JOIN product_image img ON p.id = img.product_id
      WHERE p.id = 1
      GROUP BY p.id`

var p Product
if err := sqlxdb.Get(&p, q); err != nil {
    fmt.Printf("Error: %v\n", err)
}

I get this error:

sql: Scan error on column index 26, name "images": unsupported Scan, storing driver.Value type []uint8 into type *model.ImageList

This seems like a super common scenario and yet i can't find any examples... Or finally am i even stupid for doing this because i can just do forEach in JS and do like 50 queries to fetch all the images for every product.

Just curious, if a `product_image` row is joined with `ON p.id = img.product_id` would the filter where clause `WHERE img.product_id IS NOT NULL` ever result in `false`? Isn't it unnecessary? — mkopriva, Jul 10 '20 at 16:23
You can implement the `sql.Scanner` interface for `*ImageList`, have the implementation type-assert the `interface{}` argument to `[]byte` and then pass the result of that, together with the receiver, to `json.Unmarshal`. i.e. https://play.golang.org/p/Zh50aAB3YA1 — mkopriva, Jul 10 '20 at 16:43
@mkopriva i tried but i actually get the same error, interesting. Thanks anyway for the help. And for the query if i don't write the filter, it actually gives me array with null inside like this. [null]. I guess there are other ways to this but this query works. — dankobgd, Jul 11 '20 at 10:40

score 1 · Answer 1 · answered Jul 10 '20 at 16:17

One solution for getting list of items could be done using this mapping lib: carta

q := `SELECT p.*, img.id AS img_id, img.url AS img_url 
      FROM public.product p
      LEFT JOIN product_image img ON p.id = img.product_id`

    rows, err := sqlxdb.Query(q)
    if err != nil {
        fmt.Println(err)
    }

    var products []*model.Product
    carta.Map(rows, &products)

And for the img struct i would use db:"img_id" prefix and so on, because i select with alias...

Go SQL map 1:M relation json array to slice

1 Answers1