You need to enable JavaScript to run this app.
优惠活动
大模型
产品
解决方案
定价
更多

如何将可用SQL查询转为Go GORM实现并解决Group数据为空问题

avatar
安全阿岳
2026-7-11
如何用Go GORM实现嵌套JSON聚合的SQL查询

原始SQL查询

以下SQL在DBeaver中运行正常，能查询出包含嵌套Group和Item数据的Collection列表：

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();

数据库建模

对应的PostgreSQL表结构和测试数据：

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);

遇到的问题

尝试用GORM的Raw方法执行查询时，返回结果中Group字段为空，且时间字段、CreatedBy/UpdatedBy均为零值：

尝试的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
    }
]

后端GORM模型结构体 

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
}

解决方案

问题原因分析

  1. SQL语法错误：Raw SQL中g.description与后续子查询之间缺少逗号，导致SQL执行异常，返回结果不符合预期。
  2. 字段未完整查询：原SQL仅选择了c.id、c.title和group，未获取publication_date、created_at等字段，因此这些字段被初始化为零值。
  3. 结构体映射错误：
    • gorm:"embedded"标签用于嵌入结构体到父结构体字段，不适合映射JSON类型字段，应使用json:标签对应查询列名。
    • Group结构体的TitleG与SQL返回的title字段不匹配，导致映射失败。
    • GORM默认无法自动将JSON数组解析为结构体切片，需自定义类型的Scanner和Valuer接口实现解析逻辑。

修正后的代码

1. 修正SQL查询语句

补全缺失的逗号，并查询所有需要的字段：

import "fmt"
import "database/sql/driver"
import "encoding/json"
import "time"

const sqlQuery = `
select c.id,
       c.title,
       c.publication_date,
       c.created_at,
       c.updated_at,
       c.created_by,
       c.updated_by,
       (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,
                                   created_at, updated_at, created_by, updated_by
                            from items t
                            where t.id = any (g.items_id)) items) as items,
                     g.created_at,
                     g.updated_at,
                     g.created_by,
                     g.updated_by
              from "groups" g
              where g.id = any (c.group_id)) g2) as "group"
from collections c where c.publication_date <= now();
`

2. 修正GORM结构体

添加正确的JSON映射标签，并为切片类型实现SQL解析接口：

type Collection struct {
    ID              int64           `json:"id"`
    Title           string          `json:"title"`
    PublicationDate time.Time       `json:"publication_date"`
    Group           []Group         `json:"group"`
    CreatedAt       time.Time       `json:"created_at"`
    UpdatedAt       time.Time       `json:"updated_at"`
    CreatedBy       uint            `json:"created_by"`
    UpdatedBy       uint            `json:"updated_by"`
}

// 实现Scanner接口，解析JSON数组到[]Group
func (g *[]Group) Scan(value interface{}) error {
    bytes, ok := value.([]byte)
    if !ok {
        return fmt.Errorf("failed to scan group: expected []byte, got %T", value)
    }
    return json.Unmarshal(bytes, g)
}

// 实现Valuer接口，将[]Group序列化为JSON
func (g []Group) Value() (driver.Value, error) {
    return json.Marshal(g)
}

type Group struct {
    ID          int64     `json:"id"`
    Title       string    `json:"title"`
    Price       float64   `json:"price"`
    Description string    `json:"description"`
    Items       []Item    `json:"items"`
    CreatedAt   time.Time `json:"created_at"`
    UpdatedAt   time.Time `json:"updated_at"`
    CreatedBy   uint      `json:"created_by"`
    UpdatedBy   uint      `json:"updated_by"`
}

// 实现Scanner接口，解析JSON数组到[]Item
func (i *[]Item) Scan(value interface{}) error {
    bytes, ok := value.([]byte)
    if !ok {
        return fmt.Errorf("failed to scan items: expected []byte, got %T", value)
    }
    return json.Unmarshal(bytes, i)
}

// 实现Valuer接口，将[]Item序列化为JSON
func (i []Item) Value() (driver.Value, error) {
    return json.Marshal(i)
}

type Item struct {
    ID        int64     `json:"id"`
    Size      string    `json:"size"`
    Color     string    `json:"color"`
    Quantity  int       `json:"quantity"`
    Images    []string  `json:"images"`
    CreatedAt time.Time `json:"created_at"`
    UpdatedAt time.Time `json:"updated_at"`
    CreatedBy uint      `json:"created_by"`
    UpdatedBy uint      `json:"updated_by"`
}

3. 执行查询

var products []Collection
err := p.DB.WithContext(ctx).Raw(sqlQuery).Scan(&products).Error
if err != nil {
    // 处理错误逻辑
}

内容的提问来源于stack exchange，提问作者Neifer Reverón

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
火山引擎 最新活动
全面开放
Doubao-Seedance-2.5全新模型升级

模型再升级,30秒超长叙事, 模态参考扩容

HOT
方舟Codingplan

模型自由，工具不限，最新支持 Deepseek-V4 系列、GLM-5.3 系列

HOT
方舟Agent plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列

HOT
新用户特惠专场

大模型19元起，Al应用9.9元畅享，新人首购爆款尽享优惠

最近更新时间：2026.07.11 08:54:51