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
– mkopriva, Commented Jul 10, 2020 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. play.golang.org/p/Zh50aAB3YA1 — mkopriva
– mkopriva, Commented Jul 10, 2020 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
– dankobgd, Commented Jul 11, 2020 at 10:40
The []uint8 column data for a Postgres array type has a specific format that needs to be parsed. Once parsed you'd have a [][]byte that could then be used to pass each element to json.Unmarshal. — Lee Saferite
– Lee Saferite, Commented Jul 3, 2024 at 16:08

dankobgd · Accepted Answer · 2020-07-10 16:17:13Z

2

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

answered Jul 10, 2020 at 16:17

dankobgd

4143 gold badges11 silver badges34 bronze badges

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

Comments

Lee Saferite · Accepted Answer · 2024-07-03 15:51:03Z

Your problem is you are getting back a postgres array of json data. This looks like a []uint8 to the standard scanning code. It's not as simple as adding a scanner interface to an array as mentioned in a comment. Postgres has a specific text format for passing arrays that needs to be parsed correctly.

Check out this code to understand what goes into properly parsing a Postgres array column. https://github.com/lib/pq/blob/master/array.go

Here is an example I threw together (based on some code I used recently) to demonstrate one possible way of solving your issue. I'm sure it's not optimal, but it worked in a local test.

type PqArrayOfJson[T any] []T

func (a *PqArrayOfJson[T]) Scan(src interface{}) error {
    raw := &pq.ByteaArray{}
    if err := raw.Scan(src); err != nil {
        return err
    }

    parsed := make(PqArrayOfJson[T], len(*raw))
    for i, v := range *raw {
        nv := *new(T)
        if err := json.Unmarshal(v, &nv); err != nil {
            return err
        }
        parsed[i] = nv
    }
    *a = parsed

    return nil
}

func (a PqArrayOfJson[T]) Value() (driver.Value, error) {
    mapped := make([][]byte, len(a))
    for i, v := range a {
        data, err := json.Marshal(&v)
        if err != nil {
            return nil, err
        }
        mapped[i] = data
    }
    return pq.ByteaArray(mapped).Value()
}

You'd then use something like this

type ImageList = PqArrayOfJson[*Image]

This is using the pq lib to parse the incoming array of json data into a [][]byte and then feeding each element of that into json.Unmarshal using the generic type T to create a new variable to unmarshal into.

If you use this as an idea, you can create a dedicated ImageList type and ignore all the generic aspects of the code.

Collectives™ on Stack Overflow

Go SQL map 1:M relation json array to slice

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