best practice to get nested result json

I have cases with nested results in JSON. here's the sample result,

{
  "total_result" : 25,
  "questions" : [

    {
      "id" : 1,
      "text" : "the question of user 1 here",
      "user" : {
        "id" : 5,
        "name" : "user 5",
      },
      "answers" : [
        {
          "id" : 5,
          "text" : "first answer to user 1 question",
          "user" : {
            "id" : 10,
            "name" : "user 10",
           }
        },
        {
          "id" : 6,
          "text" : "second answer to user 1 question",
          "user" : {
            "id" : 11,
            "name" : "user 11",
           }
        },
        {
          "id" : 10,
          "text" : "third answer to user 1 question",
          "user" : {
            "id" : 12,
            "name" : "user 12",
           }
        }
      ]
    },

    {
      "id" : 2,
      "text" : "the question by user 2 here",
      "user" : {
        "id" : 6,
        "name" : "user 6",
      },
      "answers" : [
        {
          "id" : 5,
          "text" : "first answer to user 2 question",
          "user" : {
            "id" : 30,
            "name" : "user 30",
           }
        },
        {
          "id" : 6,
          "text" : "second answer to user 2 question",
          "user" : {
            "id" : 20,
            "name" : "user 20",
           }
        },
        {
          "id" : 10,
          "text" : "third answer to user 2 question",
          "user" : {
            "id" : 1,
            "name" : "user 1",
           }
        }
      ]
    },

  ]
}

my struct goes here,

type Question struct {
  Id int 
  Text string ...
  User User ...
  Answer []Answer ...
}
type User struct {
  Id int ...
  Name string ...
}
type Answer struct {
  Id int ...
  Text string ...
  User User ...
}

here's the query to get questions and user.Detail

query := "select text, user_id from questions limit 10 offset 10"
rows, err, := db.QueryCtx(ctx, query)
//handel error

var questions []Question
var userIds []string
for rows.Next() {
  var q Question
  var userId string
  //scan to `question` and `userId`
  questions = append(questions, q)
  userIds = append(questions, userId)
}

here's the query to get the user to the question

query = "select name from users where id = any($1)"
userRows, err := db.QueryCtx(ctx, query, pq.Array(userIds))
//handle error
var users []User
//scan and store to users

here's the query to get answers to the question

query = "select answers.id, answers.text, u.id, u.name from answers join questions as q on q.id=answers.question_id join users as u on u.id=answers.user_id where answers.questions_id=$1"
for i := 0; i < len(questions); i++{
  rowAnswer, err := db.QueryCtx(ctx, query, questions[i].Id)
  //handle error
  var answers []Answer
  for rowAnswer.Next(){
    var answer Answer
    //scan to answer
    append = (answers, answer)
  }
  questions[i].User.Id = users[i].Id
  questions[i].User.Name = users[i].Name
  questions[i].Answer = answers
}

users table

id	name
1	name

questions table

id	text	user_id
1	text	1

answers table

id	text	question_id	user_id
1	text	1	1

the result is good, nothing wrong with the code and the result. but, I'm thinking about the n+query case, because I do looping to get the answers. my question is, is it reasonable to do that, or is any good advice for my code?

Solution

In get questions, you need var questionIds and mapQuestionIdx

add select id
questionIds is for get question id, so you can query where in
mapQuestionIdx is for save question id and index in slice. Note question_id is key and index is value.

so it look like this

query := "select id, text, user_id from questions limit 10 offset 10"
rows, err, := db.QueryCtx(ctx, query)
//handel error

var questions []Question
var userIds []string

questionIds := make([]int, 0, 10) // because limit is 10
mapQuestionIdx := make(map[int]int)
idx := 0

for rows.Next() {
  var q Question
  var userId string
  //scan to `question` and `userId`
  questions = append(questions, q)
  userIds = append(questions, userId)

  questionIds = append(questionIds, q.ID)
  mapQuestionIdx[q.ID] = idx
  idx++
}

On query to get answers to the question

add select question_id

// add select question_id
query = "select q.id question_id, answers.id, answers.text, u.id, u.name from answers join questions as q on q.id=answers.question_id join users as u on u.id=answers.user_id where answers.questions_id in ($1)"

  rowAnswer, err := db.QueryCtx(ctx, query, questionIds) // questionIds from above
  //handle error
  for rowAnswer.Next(){
    var answer Answer
    var question_id int
    //scan to answer and question_id

    i := mapQuestionIdx[question_id]
    
    questions[i].User.Id = users[i].Id
    questions[i].User.Name = users[i].Name

    if questions[i].Answer == nil {
      questions[i].Answer = make([]answer, 0)
    }
    questions[i].Answer = append(questions[i].Answer, answer)
  }