title: Converting Legacy SQL Flatten function to Standard SQL
body: <p>I have the following written in <code>#LegacySQL</code>:</p>
  
  <pre><code>   SELECT
        customer_email,
        submitted_at,
        title,
        answers.choices.labels answer_choices,
        answers.number score,
        answers.boolean true_false,
        metadata.platform device_type
      FROM
        (FLATTEN([test-test:sample.responses], answers)) resp
        LEFT JOIN [test-test:sample.forms] forms
      ON resp.answers.field.id = forms.id
      ORDER BY 1 ASC
  </code></pre>
  
  <p>It's returning the results as I want them, like so:</p>
  
  <pre><code>+------------------+-------------------------+------------------------------------------------------------+--------------------+-------+------------+-------------+
  |  customer_email  |      submitted_at       |                           title                            |   answer_choices   | score | true_false | device_type |
  +------------------+-------------------------+------------------------------------------------------------+--------------------+-------+------------+-------------+
  | myname@gmail.com | 2018-12-25 04:00:02 UTC | How would you rate this product?                           |                    |    10 |            | other       |
  | myname@gmail.com | 2018-12-25 04:00:02 UTC | What did you enjoy the most about your experience with us? | Delivery           |       |            | other       |
  | myname@gmail.com | 2018-12-25 04:00:02 UTC | What other product(s) would you like to see us make?       | Additional Colors  |       |            | other       |
  | myname@gmail.com | 2018-12-25 04:00:02 UTC | What other product(s) would you like to see us make?       | Additional Designs |       |            | other       |
  | myname@gmail.com | 2018-12-25 04:00:02 UTC | What color(s) would you want to see?                       | Green              |       |            | other       |
  +------------------+-------------------------+------------------------------------------------------------+--------------------+-------+------------+-------------+
  </code></pre>
  
  <p>I'm trying to convert to BigQuery <code>StandarSQL</code>, and I put together the following:</p>
  
  <pre><code>SELECT
    customer_email,
    submitted_at,
    title,
    answers.choices.labels answer_choices,
    answers.number score,
    answers.boolean true_false,
    metadata.platform device_type
  FROM
  `sample.responses` resp, unnest(answers) answers
  LEFT JOIN `sample.forms` forms
  ON answers.field.id  = forms.id
  ORDER BY 1 ASC
  </code></pre>
  
  <p>Unfortunately, it's returning it as records like so:</p>
  
  <pre><code>+------------------+-------------------------+-------------------------+------------------------------------------------------------+--------------------+-------+------------+-------------+
  |  customer_email  |      submitted_at       |        landed_at        |                           title                            |   answer_choices   | score | true_false | device_type |
  +------------------+-------------------------+-------------------------+------------------------------------------------------------+--------------------+-------+------------+-------------+
  | myname@gmail.com | 2018-12-25 04:00:02 UTC | 2018-12-25 03:59:07 UTC | What did you enjoy the most about your experience with us? | Delivery           | null  | null       | other       |
  | myname@gmail.com | 2018-12-25 04:00:02 UTC | 2018-12-25 03:59:07 UTC | What other product(s) would you like to see us make?       | Additional Colors  | null  | null       | other       |
  |                  |                         |                         |                                                            | Additional Designs |       |            |             |
  | myname@gmail.com | 2018-12-25 04:00:02 UTC | 2018-12-25 03:59:07 UTC | What color(s) would you want to see?                       | Green              | null  | null       | other       |
  +------------------+-------------------------+-------------------------+------------------------------------------------------------+--------------------+-------+------------+-------------+
  </code></pre>
  
  <p>What am I doing incorrectly?</p>