Question 114959 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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?

Tags:

2

Answers


  1. 0

    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)
  }
    Login or Signup to reply.
  2. 0

    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)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search