title: Ordering Inside of Array and put null or 0 for missing values
body: <p>I am having problem to order inside of an array due to the missing or null values. </p>
  
  <p>My code </p>
  
  <pre><code>SELECT
  submission_id,
  ARRAY_AGG(question_score) as questions_scores,
  SUM(question_score) as groupe_score
  
  FROM(
  SELECT
  sa.submission_id,
  qq.id as question_id,
  CASE WHEN SUM(qo.answer_points) &gt; MAX(qq.max_answer_points) THEN MAX(qq.max_answer_points) ELSE SUM(qo.answer_points) END as question_score
  FROM selection_answers sa
      inner join question_options qo on qo.id = sa.question_option_id
      inner join questions qq on qq.id = qo.question_id
  WHERE qq.task_form_id = 3306 GROUP BY 1,2 ORDER BY sa.submission_id ASC, qq.id ASC ) AS t1 GROUP BY 1
  </code></pre>
  
  <p>Output: </p>
  
  <pre><code>  submission_id |         questions_scores           |  groupe_score
   -------------------------------------------------------------------
      1034543     | {0,0,0,10,0,10,0,5,0,0,0,5,0,0,0}          |  30
      1034562     | {0,15,15,5,10,0,10,0,5,0,10,10,5,0,0}      |  85
      1034645     | {0,0,10,0,10,0,5,0,10,10,5,0,0}            |  50
      1034699     | {0,0,0,15,5,10,0,10,0,5,0,0,10,10,5,0,0,0} |  70
  </code></pre>
  
  <p>I need same number of output inside questions_scores in the same qq.id order. I've tried left and full outer join but could not find a way. Also I've created a CTE with question table and tried to join for each row in this table and CTE but could not figure it out. Thanks for your help! </p>
  
  <p>------- edit for table details---- <br>
  question table columns: </p>
  
  <pre><code>id | max_answer_points
  </code></pre>
  
  <p>question_options table columns:</p>
  
  <pre><code>id | question_id | answer_points
  </code></pre>
  
  <p>selection_answer table columns:</p>
  
  <pre><code>id| submission_id| question_option_id
  </code></pre>
  
  <p>when a submission submitted there can be multiple options selected in selected_answers or there might be no answers since question is jumped due to the logic related with a result of the previous selected question. What I aim is to show SUM(answer_points) grouped by submission and show inside of an array score of each question. </p>