OVerride type of aggregated JSON with json_agg

[svenwltr]

Hello!

I have this query:

-- name: ListSBOMComponents :many
select
    component_name,
    json_agg(version) as versions,
    json_agg(license_ids) as licenses
from sbom_components
group by component_name;

Where component_name, version and license_ids are all strings.

The resulting type is this:

type ListSBOMComponentsRow struct {
	ComponentName string `json:"componentName"`
	Versions      []byte `json:"versions"`
	Licenses      []byte `json:"licenses"`
}

I am using pgx where it is possible to specify which types JSON objects deserialize to. (so this), but I don't know what to put into the column field.

What I tried is:

          - column: "sbom_components.versions"
            go_type:
              type: "[]string"

What I expect is

type ListSBOMComponentsRow struct {
	ComponentName string   `json:"componentName"`
	Versions      []string `json:"versions"`
	Licenses      []string `json:"licenses"`
}

But that did not work as expected. Any ideas on this?

[shagohead]

It so because of sbom_components.versions is not a column. It's an aliased tuple value. In you're case you can use type override for db_type not a column. You can create that type with CREATE TYPE or just CREATE DOMAIN for existing json(b) type. I'am not sure sqlc will (un)marshall json when dealing with db_type, not column, but most likely it will.

CREATE DOMAIN versions AS jsonb;

          - db_type: "versions"
            go_type:
              type: "[]string"

[shagohead]

Or you can try:

          - db_type: "versions"
            go_type:
              type: "string"
              slice: true

[shagohead]

And of course you need to explicitly cast type versions in your query