Assign types to json aggregations

[boyswan]

What do you want to change?

AFAIK sqlc cannot assign types to json aggregations.

For example:

select 
  ...
  json_build_object(
    'id', s.id,
    'description', s.description
  ) as Show
from ...

Show will be exported as []bytes. I am able to get around this by skipping sqlc and using pgx:

v, err := pgx.CollectRows(rows, pgx.RowToAddrOfStructByName[types.PersonWithShow])

However seeing that sqlc can assign types to jsonb at a column level via overrides, I feel it would be very useful to be able to assign types to json at a query level.

What I would like to be able to do is:

  json_build_object(
    'id', s.id,
    'description', s.description
  )::Show as Show

I went down a rabbit hole trying to add custom pg types and setting db_type as an attempt for this to work in sqlc's current state, but gave up as I wasn't convinced this was the right direction.

Is this something that is possible in sqlc?

What database engines need to be changed?

PostgreSQL

What programming language backends need to be changed?

Go

[kyleconroy]

Tracking JSON unmarshaling of custom structs here: https://github.com/sqlc-dev/sqlc/issues/2761 and composite type support here #2760.

[hariangr]

Any update on this?

[asineth0]

I would really like this.