Generating Custom Stuctures for JSON data in POSTGRES, SQLC, and Golang

Question

I have a Postgres query that queries all data from table a while creating a JSON array from columns in table b.

E.g.:

SELECT 
    a.a_id, 
    a.column1, 
    COALESCE(
        json_agg(
            json_build_object(
                'b-bid', b.b_id,
                'b-name' , b.name,
            )
        ) FILTER (WHERE b.a_id IS NOT NULL),
        '[]'
    )::json AS b_data
FROM 
    a
LEFT JOIN 
    b
ON 
    a.a_id = b.a_id
WHERE 
    a.column1 = $1
GROUP BY 
    a.aid;

The corresponding sqlc structure looks like this:

type GetAByIdRow struct {
    AId            uuid.UUID        `json:"a_id"`
    Column1        pgtype.Text      `json:"column1"`
    BData          []byte           `json:"b_data"`
}

How do I configure sqlc to emit structs of []B instead of []byte?

I tried sqlc.embed which creates a single struct of another table. I need a slice.

I also tried overriding types. It doesn't work as it's only meant for a column(or type) where as I'm running a join here.

My expected output:

type GetAByIdRow struct {
    Aid            uuid.UUID        `json:"a_id"`
    Column1        pgtype.Text      `json:"column1"`
    BData          []B              `json:"b_data"`
}

I appreciate any help you can give me. Thanks!

Answer 1

I also tried overriding types. It doesn't work as it's only meant for a column(or type) where as I'm running a join here.

Then just create a view and override the column type that way. e.g.

schema

CREATE TABLE a (
    a_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    column1 TEXT NOT NULL
);

CREATE TABLE b (
    b_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    a_id UUID REFERENCES a(a_id),
    name TEXT NOT NULL
);

CREATE VIEW a_view AS
SELECT 
    a.a_id, 
    a.column1, 
    COALESCE(
        json_agg(
            json_build_object(
                'b-bid', b.b_id,
                'b-name', b.name
            ) FILTER (WHERE b.a_id IS NOT NULL),
             '[]'
        )
    )::json AS b_data
FROM 
    a
LEFT JOIN 
    b
ON 
    a.a_id = b.a_id
GROUP BY 
    a.a_id;

query

-- name: GetAById :many
SELECT * FROM a_view WHERE column1 = $1;

sqlc.json

{
  "version": "2",
  "sql": [
    {
      "schema": "schema.sql",
      "queries": "query.sql",
      "engine": "postgresql",
      "gen": {
        "go": {
          "sql_package": "db",
          "out": "db",
          "emit_json_tags": true,
          "emit_db_tags": true,
          "overrides": [
            {
              "column": "a_view.b_data",
              "go_type": {
                "type": "B",
                "slice": true
              }
            }
          ]
        }
      }
    }
  ]
}

Which will give you what you want

type AView struct {
    AID     uuid.UUID `db:"a_id" json:"a_id"`
    Column1 string    `db:"column1" json:"column1"`
    BData   []B       `db:"b_data" json:"b_data"`
}

I made a working playground of all this here - https://play.sqlc.dev/p/f9ea4509bd5621868ca160327bdda1bf3cc90bf1cdaa19d925c290fd039d4810