title: Migrating from Legacy SQL: options for "WITHIN RECORD" with Standard SQL
body: <p>I am trying to migrate to Standard SQL from BigQuery Legacy SQL. The Legacy product offered the ability to query "WITHIN RECORD" which came in handy on  numerous occasions. </p>
  
  <p>I am looking for an efficient alternative to WITHIN RECORD. I could always just use a few subqueries and join them but wondering if there may be a more efficient way using ARRAY + ORDINAL. </p>
  
  <p><strong>EXAMPLE: Consider the following Standard SQL</strong> </p>
  
  <pre><code>WITH
    sessPageVideoPlays AS (
      SELECT fullVisitorId, visitNumber, h.page.pagePath,
          # This would previously use WITHIN RECORD in Legacy SQL:
          ARRAY( SELECT eventInfo.eventAction FROM UNNEST(hits)
                 WHERE eventInfo.eventCategory="videoPlay" 
                 ORDER BY hitNumber DESC
          )[ORDINAL(1)] AS lastVideoSeen
      FROM
            `proj.ga_sessions`, UNNEST(hits) as h
      GROUP BY fullVisitorId, visitNumber, h.page.pagePath, lastVideoSeen
    )
  SELECT 
    pagePath, lastVideoSeen, numOccur  
  FROM 
       (SELECT    
          pagePath, lastVideoSeen, count(1) numOccur
        FROM
          sessPageVideoPlays
        GROUP BY
          pagePath, lastVideoSeen
  )
  </code></pre>
  
  <p><strong>Resulting output:</strong></p>
  
  <p><img src="https://i.imgur.com/we2wA0X.png" alt="Table 1"></p>
  
  <p><strong>Questions:</strong></p>
  
  <p>1) I would like to see the last video play event on a given page, which is what I used accomplish using WITHIN RECORD but am attempting the ARRAY + ORDINAL approach shown above. However for this to work, I'm thinking the SELECT statement within ARRAY() must get synchronized to the outer record since it is now flattened? Is that accurate? </p>
  
  <p>2) I would also like get a COUNT of DISTINCT videos played on a given page and wondering if more efficient approach would be joining to a separate query OR inserting another inline aggregate function, like done with ARRAY above.</p>
  
  <p>Any suggestions would be appreciated.</p>