record and record[] fields coming back as text

[alekangelov]

is there any way for a string to become an sqlx.Row?

Here's a minimal reproduction with a database reproduction

create table
  chats (
    id serial primary key,
    name varchar(255) not null,
    created_at timestamp not null default now()
  )

create table
  participants (
    chat_id integer not null references chats(id),
    user_id integer not null references users(id),
    created_at timestamp not null default now()
    primary key (chat_id, user_id)
  )

create table
  user (
    id serial primary key,
    name varchar(255) not null,
    created_at timestamp not null default now()
  )

-- dummy data
insert into chats (name) values ('chat1'), ('chat2'), ('chat3')

insert into users (name) values ('user1'), ('user2'), ('user3')

insert into participants (chat_id, user_id) values (1, 1), (1, 2), (2, 2), (2, 3), (3, 1), (3, 3)

Here's the go code

package main

import (
	"log"

	_ "github.com/jackc/pgx/v5/stdlib" // load pgx driver for PostgreSQL
	"github.com/jmoiron/sqlx"
)

// Define structs to map to database tables
type Chat struct {
	ID        int    `db:"id"`
	Name      string `db:"name"`
	CreatedAt string `db:"created_at"`
	// THIS DOES NOT WORK
	// Participants []Participant `db:"participants"`
	// THIS WORKS, but it shouldn't be necessary to marshal/unmarshal
	Participants string
}

type Participant struct {
	ChatID    int    `db:"chat_id"`
	UserID    int    `db:"user_id"`
	CreatedAt string `db:"created_at"`
}

func main() {
	// Connection string

	log.Printf("postgres://postgres:postgres@localhost:5432/xdw")

	// Open a connection to the database
	db, err := sqlx.Connect("pgx", "postgres://postgres:postgres@localhost:5432/xdw")
	if err != nil {
		panic(err)
	}
	query := `SELECT
			chats.id,
			chats.name,
			chats.created_at,
			array_agg(
				ROW(
					participants.chat_id,
					participants.user_id,
					participants.created_at
				)
			) as participants
		FROM
			public.chats
		JOIN public.participants ON chats.id = participants.chat_id
		GROUP BY
			chats.id,
			chats.name,
			chats.created_at`

	var chats []Chat

	rows, err := db.Queryx(query)

	if err != nil {
		panic(err)
	}

	for rows.Next() {
		var chat Chat
		err = rows.StructScan(&chat)
		if err != nil {
			panic(err)
		}
	}

	if len(chats) == 0 {
		panic("no chats found")
	}

	// Iterate over the results and print the data
	for _, chat := range chats {
		log.Printf("Chat ID: %d\n", chat.ID)
		log.Printf("Chat Name: %s\n", chat.Name)
		log.Printf("Created At: %s\n", chat.CreatedAt)

		// Iterate over the participants and print each participant
		for _, participant := range chat.Participants {
			log.Printf("Participant: %+v\n", participant)
		}

		log.Println("---------------")
	}
}

Is there any way to parse a string as a sqlx.Row or natively scan a struct from a subrecord? Am I missing something. The workaround I found was to jsonb_agg(json_build_object()) and unmarshall.