title: Bigquery: Group query results in arrays
body: <p>I have a table that lists friends from a particular user:</p>
  <p>user_id | friend_name</p>
  <p>1       | JOEL</p>
  <p>1       | JACK</p>
  <p>2       | MARIA</p>
  <p>I want to have them grouped by user_id and each row has an array with all the friends.</p>
  <p>How can make a selection that would do this transformation?</p>
  <p>UPDATE:</p>
  <p>select user_id, array_agg(friend_name) as friends
  from your_table
  group by user_id</p>
  <p>Works fine.</p>
  <p>However I forgot a small detail, the table has another column.</p>
  <p>user_id | friend_name | friends_age</p>
  <p>1       | JOEL   | 21</p>
  <p>1       | JACK   | 30</p>
  <p>2       | MARIA  | 25</p>
  <p>My solution was to add another array_agg:</p>
  <pre><code>select user_id, array_agg(friend_name),  array_agg(friend_age)as friends
  from your_table
  group by user_id
  </code></pre>
  <p>I believe it works, the only problem is when age is Null, in that case, I need to add a CASE WHEN clause.</p>
  <pre><code>select user_id, array_agg(friend_name),  
  array_agg(CASE friend_age IS NULL THEN 0 ELSE friend_age END)as friends
  from your_table
  group by user_id
  </code></pre>