我有这个SQL查询。

select c.id,
       c.title as collection,
               (select json_agg(g2.*)
                from (select g.id,
                             g.title,
                             g.price,
                             g.description,
                             (select json_agg(items.*)
                              from (select id, size, color, quantity, images
                                    from items t
                                    where t.id = any (g.items_id)) items) as items
                      from "groups" g
                      where g.id = any (c.group_id)) g2) "group"
from collections c where c.publication_date <= now();

这是我在数据库中的模型。

create table collections (
    id serial primary key NOT null,
    title varchar (20) unique not null,
    group_id int[],
    publication_date timestamp not null,
    created_at timestamp NOT null default now(),
    updated_at timestamp NOT null default now(),
    created_by int NOT NULL,
    updated_by int NOT NULL
    );
insert into collections(title, group_id, publication_date, created_by, updated_by)
values
('verano', '{1,2}',  now(),  1, 1),
('premium', '{2}', now(), 1, 1),
('moda 2023','{3}', now(), 1, 1),
('moda 2024','{4}', now(), 1, 1);
create table "groups" (
    id serial primary key NOT null,
    title varchar (50) unique not null,
    items_id int[],
    price float8 not null,
    description varchar (250),
    created_at timestamp NOT null default now(),
    updated_at timestamp NOT null default now(),
    created_by int  NOT NULL,
    updated_by int  NOT NULL
);
insert into "groups"(title, items_id, price, description ,created_by, updated_by)
values
('Blusa De Dama Mikey Mouse', '{1,2}', 5.99,'Blusa de dama con micro durazno, distintos colores.' , 1, 1),
('Sueter Tricolor', '{3,4}', 13.85,'Blusa de dama con micro durazno, distintos colores.' , 1, 1),
('Mono Adidas', '{5,6}', 17.99,'Blusa de dama con micro durazno, distintos colores.' , 1, 1),
('Franela Apolo', '{}', 8.99,'Franela Apolo, distintos colores.' , 1, 1);
create table items (
  id serial primary key NOT null,
  "size" char not null,
  color varchar (10) not null,
  quantity int not null,
  images text[] not null,
  created_at timestamp NOT null default now(),
  updated_at timestamp NOT null default now(),
  created_by int  NOT NULL,
  updated_by int  NOT NULL
  );
insert into items (size, color, quantity, images, created_by, updated_by)
values
('U', 'Negro', 150, '{none}', 1, 1),
('S', 'Blanco', 150, '{none}', 1, 1),
('M', 'Gris', 150, '{none}', 1, 1),
('U', 'Gris', 220, '{none}', 1, 1),
('S', 'Verde', 220, '{none}', 1, 1),
('M', 'Negro', 220, '{none}', 1, 1);

当我在DBeaver中使用该模型时，上面的查询可以很好地工作，但我不知道如何在Go with GORM中使用它。
我尝试了这种方法，但组数据返回为空。

err := p.DB.WithContext(ctx).Raw("select c.id, c.title, (select json_agg(g2.*) from (select g.id, g.title, g.price, g.description (select json_agg(items.*) from (select id, size, color, quantity, images from items t where t.id = any (g.items_id)) items) as items from groups g where g.id = any (c.group_id)) g2) as group from collections c where c.publication_date <= now()").
Scan(&products).
Error

这就是我得到的结果。

[
    {
        "ID": 1,
        "Title": "verano",
        "PublicationDate": "0001-01-01T00:00:00Z",
        "Group": null,
        "CreatedAt": "0001-01-01T00:00:00Z",
        "UpdatedAt": "0001-01-01T00:00:00Z",
        "CreatedBy": 0,
        "UpdatedBy": 0
    },
    {
        "ID": 2,
        "Title": "premium",
        "PublicationDate": "0001-01-01T00:00:00Z",
        "Group": null,
        "CreatedAt": "0001-01-01T00:00:00Z",
        "UpdatedAt": "0001-01-01T00:00:00Z",
        "CreatedBy": 0,
        "UpdatedBy": 0
    },
    {
        "ID": 3,
        "Title": "moda 2023",
        "PublicationDate": "0001-01-01T00:00:00Z",
        "Group": null,
        "CreatedAt": "0001-01-01T00:00:00Z",
        "UpdatedAt": "0001-01-01T00:00:00Z",
        "CreatedBy": 0,
        "UpdatedBy": 0
    },
    {
        "ID": 4,
        "Title": "moda 2024",
        "PublicationDate": "0001-01-01T00:00:00Z",
        "Group": null,
        "CreatedAt": "0001-01-01T00:00:00Z",
        "UpdatedAt": "0001-01-01T00:00:00Z",
        "CreatedBy": 0,
        "UpdatedBy": 0
    }
]

这是我在后端使用的模型。

type Collection struct {
    ID              int64
    Title           string
    PublicationDate time.Time
    Group           []Group `gorm:"embedded"`
    CreatedAt       time.Time
    UpdatedAt       time.Time
    CreatedBy       uint
    UpdatedBy       uint
}
type Group struct {
    ID          int64
    TitleG      string
    Price       float64
    Description string
    Items       []Item `gorm:"embedded"`
    CreatedAt   time.Time
    UpdatedAt   time.Time
    CreatedBy   uint
    UpdatedBy   uint
}
type Item struct {
    ID        int64
    Size      string
    Color     string
    Quantity  int
    Images    []string
    CreatedAt time.Time
    UpdatedAt time.Time
    CreatedBy uint
    UpdatedBy uint
}

我期待着你的评论。谢谢你，谢谢！