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?

CodePudding user response：

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

CodePudding user response：

You can make use of a combination of struct tag and encoding/json to un-marshal your result properly.

Example for your use case:

Redeclare your type as

type Question struct {
  Id int `json:"id"`
  Text string `json:"text"`
  User User `json:"user"`
  Answer []Answer `json:"answer"`

// Fill the rest yourself
...
}

To unmarshal simply:

 someDummyQuestionsResult := []byte{
    {
     // some example
    }
 }

 var questions []Question
 err := json.Unmarshal(someDummyQuestionsResult , &questions)

 fmt.Println(questions)