title: How can I generate a UID on-the-fly in bigquery SQL?
body: <p>I am trying to join a table with itself. Here is a MWE of the problem:</p>
  <pre><code>WITH elems as (
      SELECT letter, generate_uuid() randomid
  FROM 
    UNNEST(SPLIT('aabcdefghij', '')) letter
  ),
  
  l as (SELECT * FROM ten_elems),
  r as (SELECT * FROM ten_elems)
  
  --SELECT * FROM l INNER JOIN r on l.randomid = r.randomid
  SELECT * FROM l INNER JOIN r on l.letter = r.letter
  </code></pre>
  <p>If you run this, you will see that the random IDs on the left and on the right are different. Obviously if, instead, you uncommment the other join, it returns no results. The same happens for <code>row_number() OVER ()</code>, and because my top level elements are not unique I cannot simply use <code>row_number() OVER (ORDER BY letter)</code> as it will still (potentially) assign different IDs to the two &quot;a&quot; entries.</p>
  <p>The actual table is obviously way more complex, and contains arrays of arrays. However, as here, the top level elements are not necessarily unique, so I need to generate UIDs before unnesting, so I can later join them together correctly.</p>
  <p>I understand that a work-around would be to save the table <em>with</em> the UID first, and then do the self-join, but I had hoped I wouldn't need to do that, as in general this data doesn't need identification at this level. So if there is some way of making the UID persistent through my queries, rather than generated anew on-demand, it would really help me.</p>