Golang中从查询结果获取嵌套JSON的最佳实践

在Golang中处理嵌套JSON返回，最佳实践是使用JOIN查询配合手动数据组装。以下是具体实现方案：

// 定义响应结构体
type PostResponse struct {
    TotalResult int    `json:"total_result"`
    CurrentPage int    `json:"current_page"`
    Posts       []Post `json:"posts"`
}

// 查询并组装数据的实现
func GetPostsWithDetails(ctx context.Context, db *sql.DB, page, limit int) (*PostResponse, error) {
    offset := (page - 1) * limit
    
    // 查询帖子总数
    var total int
    err := db.QueryRowContext(ctx, "SELECT COUNT(*) FROM posts").Scan(&total)
    if err != nil {
        return nil, err
    }
    
    // 使用JOIN查询获取所有相关数据
    query := `
        SELECT 
            p.id, p.title,
            a.id, a.name,
            c.id, c.text
        FROM posts p
        LEFT JOIN authors a ON p.author_id = a.id
        LEFT JOIN comments c ON p.id = c.post_id
        WHERE p.id IN (
            SELECT id FROM posts 
            ORDER BY id 
            LIMIT $1 OFFSET $2
        )
        ORDER BY p.id, c.id
    `
    
    rows, err := db.QueryContext(ctx, query, limit, offset)
    if err != nil {
        return nil, err
    }
    defer rows.Close()
    
    // 使用map临时存储数据
    postsMap := make(map[int]*Post)
    
    for rows.Next() {
        var postID int
        var postTitle string
        var authorID sql.NullInt64
        var authorName sql.NullString
        var commentID sql.NullInt64
        var commentText sql.NullString
        
        err := rows.Scan(
            &postID, &postTitle,
            &authorID, &authorName,
            &commentID, &commentText,
        )
        if err != nil {
            return nil, err
        }
        
        // 如果帖子不存在于map中，创建新条目
        if _, exists := postsMap[postID]; !exists {
            postsMap[postID] = &Post{
                ID:    postID,
                Title: postTitle,
                Author: Author{
                    ID:   int(authorID.Int64),
                    Name: authorName.String,
                },
                Comment: []Comment{},
            }
        }
        
        // 添加评论（如果存在）
        if commentID.Valid && commentText.Valid {
            comment := Comment{
                ID:   int(commentID.Int64),
                Text: commentText.String,
            }
            postsMap[postID].Comment = append(postsMap[postID].Comment, comment)
        }
    }
    
    // 将map转换为切片
    posts := make([]Post, 0, len(postsMap))
    for _, post := range postsMap {
        posts = append(posts, *post)
    }
    
    // 构建响应
    response := &PostResponse{
        TotalResult: total,
        CurrentPage: page,
        Posts:       posts,
    }
    
    return response, nil
}

对于更复杂的嵌套结构，可以使用JSON聚合函数（如果数据库支持）：

// PostgreSQL示例使用json_agg
func GetPostsWithJSONAgg(ctx context.Context, db *sql.DB, page, limit int) (*PostResponse, error) {
    offset := (page - 1) * limit
    
    query := `
        SELECT 
            p.id,
            p.title,
            json_build_object(
                'id', a.id,
                'name', a.name
            ) as author,
            COALESCE(
                json_agg(
                    json_build_object('id', c.id, 'text', c.text)
                ) FILTER (WHERE c.id IS NOT NULL),
                '[]'
            ) as comments
        FROM posts p
        LEFT JOIN authors a ON p.author_id = a.id
        LEFT JOIN comments c ON p.id = c.post_id
        GROUP BY p.id, a.id, p.title
        ORDER BY p.id
        LIMIT $1 OFFSET $2
    `
    
    rows, err := db.QueryContext(ctx, query, limit, offset)
    if err != nil {
        return nil, err
    }
    defer rows.Close()
    
    var posts []Post
    for rows.Next() {
        var post Post
        var authorJSON []byte
        var commentsJSON []byte
        
        err := rows.Scan(
            &post.ID,
            &post.Title,
            &authorJSON,
            &commentsJSON,
        )
        if err != nil {
            return nil, err
        }
        
        // 解析JSON到结构体
        json.Unmarshal(authorJSON, &post.Author)
        json.Unmarshal(commentsJSON, &post.Comment)
        
        posts = append(posts, post)
    }
    
    // 查询总数
    var total int
    err = db.QueryRowContext(ctx, "SELECT COUNT(*) FROM posts").Scan(&total)
    if err != nil {
        return nil, err
    }
    
    return &PostResponse{
        TotalResult: total,
        CurrentPage: page,
        Posts:       posts,
    }, nil
}

使用sqlx库可以简化代码：

import "github.com/jmoiron/sqlx"

func GetPostsWithSqlx(ctx context.Context, db *sqlx.DB, page, limit int) (*PostResponse, error) {
    offset := (page - 1) * limit
    
    query := `
        SELECT 
            p.id, p.title,
            a.id as "author.id", 
            a.name as "author.name",
            json_agg(
                json_build_object('id', c.id, 'text', c.text)
            ) as comments
        FROM posts p
        LEFT JOIN authors a ON p.author_id = a.id
        LEFT JOIN comments c ON p.id = c.post_id
        GROUP BY p.id, a.id, p.title
        ORDER BY p.id
        LIMIT $1 OFFSET $2
    `
    
    var posts []Post
    err := db.SelectContext(ctx, &posts, query, limit, offset)
    if err != nil {
        return nil, err
    }
    
    var total int
    err = db.GetContext(ctx, &total, "SELECT COUNT(*) FROM posts")
    if err != nil {
        return nil, err
    }
    
    return &PostResponse{
        TotalResult: total,
        CurrentPage: page,
        Posts:       posts,
    }, nil
}

这些方法避免了N+1查询问题，通过单次数据库查询获取所有需要的数据，然后通过代码逻辑组装成嵌套结构。