golang sqlc nested data as array

Question

Im using sqlc with postgres in my golang app. And i try to implement simple logic, that will returns to me array of products with nested array of product categories inside. I saw that question. But it doesn't help. Here is my tables:

create table products
(
    id    serial primary key,
    title text unique not null,
    url   text
);

create table product_categories
(
    id         serial primary key,
    title      text unique not null,
    product_id integer     not null
        constraint products_id_fk references products (id),
    url        text

);

Here is query:

select p.*, sqlc.embed(pc)
from products p
         join product_categories pc on pc.product_id = p.id

and i expect generated struct like this:

type GetAllProductsAndSubcatsRow struct {
    ID                int32           `db:"id" json:"id"`
    Title             string          `db:"title" json:"title"`
    Url               pgtype.Text     `db:"url" json:"url"`
    ProductCategory []ProductCategory `db:"product_category" json:"product_category"`
}

but i got:

type GetAllProductsAndSubcatsRow struct {
    ID              int32           `db:"id" json:"id"`
    Title           string          `db:"title" json:"title"`
    Url             pgtype.Text     `db:"url" json:"url"`
    ProductCategory ProductCategory `db:"product_category" json:"product_category"`
}

can't figure out what am i do wrong?

upd.

@Fraser answer this is the expected way to fix that issue, but sqlc has own opinion about it :)

usign array_agg postgres function doesn't help much, because generated struct looks like this

type GetAllProductsAndSubcatsRow struct {
    ID              int32           `db:"id" json:"id"`
    Title           string          `db:"title" json:"title"`
    Url             pgtype.Text     `db:"url" json:"url"`
    ProductCategory interface{}     `db:"product_category" json:"product_category"`
}

Answer 1

The sqlc.embed(pc) doesn't make sense in this context, it is for embedding a single struct, not for creating a slice of structs.

To work around this issue you can create a view that creates the product_categories column as a json type, then use the overrides feature of sqlc to cast this as a slice of ProductCategory. e.g.

CREATE TABLE products
(
    id    serial primary key,
    title text unique not null,
    url   text
);

CREATE TABLE product_categories
(
    id         serial primary key,
    title      text unique not null,
    product_id integer not null constraint products_id_fk references products (id),
    url        text
);

CREATE VIEW product_view AS
SELECT
    p.id,
    p.title,
    p.url,
    JSON_AGG(pc.*) AS product_categories
FROM
    products p
JOIN
    product_categories pc ON pc.product_id = p.id
GROUP BY
    p.id, p.title, p.url;

This simplifies the query to

-- name: GetProductsWithCategories :many
SELECT * FROM product_view;

Now you need to override the type of your product_view.product_categories column in your sqlc.(yaml|yml) or sqlc.json file, I use json in this example.

{
  "version": "2",
  "sql": [
    {
      "schema": "schema.sql",
      "queries": "query.sql",
      "engine": "postgresql",
      "gen": {
        "go": {
          "sql_package": "pgx/v5",
          "out": "db",
          "emit_json_tags": true,
          "emit_db_tags": true,
          "overrides": [
            {
              "column": "product_view.product_categories",
              "go_type": {
                "type": "ProductCategory",
                "slice": true
              }
            }
          ]
        }
      }
    }
  ]
}

This should give you models like so, with the correct []ProductCategory slice on the ProductView struct.

type Product struct {
    ID    int32       `db:"id" json:"id"`
    Title string      `db:"title" json:"title"`
    Url   pgtype.Text `db:"url" json:"url"`
}

type ProductCategory struct {
    ID        int32       `db:"id" json:"id"`
    Title     string      `db:"title" json:"title"`
    ProductID int32       `db:"product_id" json:"product_id"`
    Url       pgtype.Text `db:"url" json:"url"`
}

type ProductView struct {
    ID                int32                `db:"id" json:"id"`
    Title             string               `db:"title" json:"title"`
    Url               pgtype.Text          `db:"url" json:"url"`
    ProductCategories []ProductCategory    `db:"product_categories" json:"product_categories"`
}

This should now be correctly scanned with the generated function which should look something like...

func (q *Queries) GetProductsWithCategories(ctx context.Context) ([]ProductView, error) {
    rows, err := q.db.Query(ctx, getProductsWithCategories2)
    if err != nil {
        return nil, err
    }
    defer rows.Close()
    var items []ProductView
    for rows.Next() {
        var i ProductView
        if err := rows.Scan(
            &i.ID,
            &i.Title,
            &i.Url,
            &i.ProductCategories,
        ); err != nil {
            return nil, err
        }
        items = append(items, i)
    }
    if err := rows.Err(); err != nil {
        return nil, err
    }
    return items, nil
}

I created a working playground version of all this here https://play.sqlc.dev/p/6a5d01a67c3569b16c77a8a47ac893093ecffa6e1ef36ac569a6ef4dcf4aef90