title: How do i join more than 2 postgres tables and get the result as nestedjson in postgres
body: <pre><code>select categories.*, array_agg(questions.r_question) as theQuestions from categories
  inner join
      (select questions.*, json_build_object('question', questions.question) as r_question,
              array_agg(answers.ans) as answeeer from questions)
          questions on categories.category_id = questions.category_id
  inner join
      (select answers.*, json_build_object('answer', answers.answer) as ans from answers)
          answers on questions.question_id = answers.question_id
  group by categories.category_id;
  </code></pre>
  
  <p>I want to have an array of Categories that embedded questions and questions embedded answers</p>
  
  <p>tables structure</p>
  
  <p><div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
  <div class="snippet-code">
  <pre class="snippet-code-html lang-html prettyprint-override"><code>&lt;pre&gt;
  
  
  quizx=# select * from categories
  quizx-# ;
   category_id |       title       |          description
  -------------+-------------------+--------------------------------
             1 | Computer scicence | Quiz based on computer science
             2 | General culture   | Quiz based on general reality
             3 | Chemistry         | Quiz based on chemistry and so
             8 | Capstone          | Quiz based on chemistry and so
  (4 rows)
  
  
  quizx=# select * from answers;
   answer_id |          answer          | correct | question_id
  -----------+--------------------------+---------+-------------
           1 | Instructions             |       0 |           1
           7 | Line length and wrapping |       0 |           2
           8 | Control Structure        |       1 |           2
           4 | All of the above         |       1 |           1
           6 | Functions                |       0 |           2
           5 | Comments                 |       0 |           2
           3 | Documents                |       0 |           1
           2 | Data Structures          |       0 |           1
          14 | cool stuff               |       0 |           2
  (9 rows)
  
  
  quizx=# select * from questions;
   question_id |                                                    question                                                    | category_id
  -------------+----------------------------------------------------------------------------------------------------------------+-------------
             1 | Software is defined as ____                                                                                    |           1
             2 | The rules of writing 'if-then-else', 'case-switch', 'while-until' and 'for' control flow statements are called |           1
             4 | Who is it??                                                                                                    |           1
             5 | slam                                                                                                           |           2
             6 | Is it good???                                                                                                  |           2
             7 | test                                                                                                           |           2
             8 | again                                                                                                          |           2
  (7 rows)
  
  &lt;/pre&gt;</code></pre>
  </div>
  </div>
  </p>
  
  <p>Expected result from my query:</p>
  
  <p><div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
  <div class="snippet-code">
  <pre class="snippet-code-html lang-html prettyprint-override"><code>&lt;pre&gt;
  
  [
      {
          "categoryId": 1,
          "theCategory": "computer science ",
          "description": "stuff about code\r\n",
          "questions": [
              {
                  "questionId": 9,
                  "theQuestion": "what year was the video game crash?",
                  "answers": [
                      {
                          "answerId": 22,
                          "theAnswer": "1985",
                          "correctAnswer": false
                      },
                      {
                          "answerId": 23,
                          "theAnswer": "1994",
                          "correctAnswer": false
                      },
                      {
                          "answerId": 24,
                          "theAnswer": "1983",
                          "correctAnswer": true
                      },
                      {
                          "answerId": 25,
                          "theAnswer": "1977",
                          "correctAnswer": false
                      }
                  ]
              },
              {
                  "questionId": 11,
                  "theQuestion": "what language was the first commercially available?",
                  "answers": [
                      {
                          "answerId": 30,
                          "theAnswer": "java script",
                          "correctAnswer": false
                      },
                      {
                          "answerId": 31,
                          "theAnswer": "C",
                          "correctAnswer": false
                      },
                      {
                          "answerId": 32,
                          "theAnswer": "FORTRAN",
                          "correctAnswer": true
                      },
                      {
                          "answerId": 33,
                          "theAnswer": "ada",
                          "correctAnswer": false
                      }
                  ]
              },
              {
                  "questionId": 12,
                  "theQuestion": "what is COBAL?",
                  "answers": [
                      {
                          "answerId": 34,
                          "theAnswer": "a business language used in many  machines  ",
                          "correctAnswer": true
                      },
                      {
                          "answerId": 35,
                          "theAnswer": "the first mainframe ",
                          "correctAnswer": false
                      },
                      {
                          "answerId": 36,
                          "theAnswer": "the first computer introduced ",
                          "correctAnswer": false
                      },
                      {
                          "answerId": 37,
                          "theAnswer": "the computer that introduced arpnet to the us military ",
                          "correctAnswer": false
                      }
                  ]
              }
          ]
      },
      {
          "categoryId": 4,
          "theCategory": "video games ",
          "description": "this is about video games \r\n",
          "questions": [
              {
                  "questionId": 10,
                  "theQuestion": "what game was the iconic character Mario's first appearance?",
                  "answers": [
                      {
                          "answerId": 26,
                          "theAnswer": "donkey kong ",
                          "correctAnswer": true
                      },
                      {
                          "answerId": 27,
                          "theAnswer": "super mario bros ",
                          "correctAnswer": false
                      },
                      {
                          "answerId": 28,
                          "theAnswer": "mario bros",
                          "correctAnswer": false
                      },
                      {
                          "answerId": 29,
                          "theAnswer": "donkey kong country",
                          "correctAnswer": false
                      }
                  ]
              }
          ]
      }
  ]
  
  &lt;pre&gt;</code></pre>
  </div>
  </div>
  </p>
  
  <p>I am getting the following error:
  ERROR:  missing FROM-clause entry for table "answers"
  LINE 7:             array_agg(answers.ans) as answeeer from question...
                                ^
  SQL state: 42P01
  Character: 212</p>