best practice to get nested result json

Question

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?

Answer 1

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