sql - 如何返回嵌套的JSON？

我在 PostgreSQL 数据库中有3个表。

QUESTIONS 表:

| id (int) | text (text)                          |
|----------|--------------------------------------|
| 1        | What is your favorite color?         |
| 2        | What is your favorite football club? |

选项表:

| id (int) | text (text) |
|----------|-------------|
| 1        | red         |
| 2        | blue        |
| 3        | grey        |
| 4        | green       |
| 5        | brown       |

QUESTIONS_OPTIONS 表:

| question_id (int) | option_id (int) |
|-------------------|-----------------|
| 1                 | 1               |
| 1                 | 2               |
| 1                 | 3               |
| 1                 | 4               |
| 1                 | 5               |

在Golang应用程序中，我创建了以下模型:

type Option struct {
    ID   int    `json:"option_id"`
    Text string `json:"option_text"`
}

type Question struct {
    ID int `json:"question_id"`
    Text string `json:"question_text"`
    Options []Option `json:"options"`
}

在控制器中，我有这样的代码:

var GetQuestions = func(responseWriter http.ResponseWriter, request *http.Request) {
    rows, _ := database.DBSQL.Query("SELECT * FROM questions;")
    defer rows.Close()

    var questions []Question

    for rows.Next() {
        var question Question
        var options []Option

        queries, _ := database.DBSQL.Query(`select options.id as option_id, options.text as option_text from questions_options inner join questions on questions_options.question_id = ` + &question.ID + ` inner join options on questions_options.option_id = options.id`)
        queries.Close()

        for queries.Next() {
            var option Option
            if err := queries.Scan(&option.ID, &option.Text); err != nil {
                log.Println(err)
            }
            options = append(options, option)
        }

        if err := rows.Scan(&question.ID, &question.Text, options); err != nil {  // service raise error in this line: sql: expected 2 destination arguments in Scan, not 3
            log.Println(err)
        }

        questions = append(questions, question)
    }
    utils.Response(responseWriter, http.StatusOK, questions)
}

当我尝试发出GET请求以使用所有选项服务来回答所有问题时，例如错误的结果:

[
    {
        "question_id": 0,
        "question_text": "",
        "options": null
    },
    {
        "question_id": 0,
        "question_text": "",
        "options": null
    }
]

我在哪里犯错？

最佳答案

您应该将queries.Close()移动到循环的结尾，如下所示:

var GetQuestions = func(responseWriter http.ResponseWriter, request *http.Request) {
    rows, _ := database.DBSQL.Query("SELECT * FROM questions;")
    defer rows.Close()

    var questions []Question

    for rows.Next() {
        var question Question

        if err := rows.Scan(&question.ID, &question.Text); err != nil {
            log.Println(err)
            continue
        }

        queries, _ := database.DBSQL.Query(`select options.id as option_id, options.text as option_text from questions_options inner join questions on questions_options.question_id = $1 inner join options on questions_options.option_id = options.id`, question.ID)

        for queries.Next() {
            var option Option
            if err := queries.Scan(&option.ID, &option.Text); err != nil {
                log.Println(err)
            }
            question.Options = append(question.Options, option)
        }
        queries.Close()

        questions = append(questions, question)
    }
    utils.Response(responseWriter, http.StatusOK, questions)
}

关于sql - 如何返回嵌套的JSON？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/54997768/