title: Transform data in Google bigquery - extract text, split it into multiple columns and pivoting the data
body: <p>I have some weblog data in big query which I need to transform to make it easier to use and query. The data looks like:</p>
  
  <p><img src="https://i.stack.imgur.com/nPjBX.png" alt="Big_Query_Table"></p>
  
  <p>I want to extract and transform the data <strong>within the curled brackets after Results{…..}</strong> (colored blue). The data is of the form <strong>‘(\d+((PQ)|(KL))+\d+)’</strong> and there can be 1-20+ entries in the result array. I am only interested in the first 16 entries.</p>
  
  <p>I have been able to extract the data within curled brackets into a new column, using Substr and regext_extract. But I'm unable to SPLIT it into columns (sometimes there is only 1 result and so the delimiter "," is missing. I'm new with regex, may be I can use something like ‘(\d+((PQ)|(KL))+\d+)<strong>{1}’</strong> etc. to split the data into multiple columns and then pivot it.</p>
  
  <p>Ideal output in my case would be to transform it into something like:</p>
  
  <p><img src="https://i.stack.imgur.com/zqtTJ.png" alt="Ouput Table"></p>
  
  <p>In the above solution, each row in original table is repeated from 1-16 times depending on the number of items in the Results array.</p>
  
  <p>I’m not completely sure if it’s possible to do this in big query. I’ll be grateful if anyone can help me out a little here.</p>
  
  <p>If this is not possible, then I can have 16 rows for every event with NULL values in Event_details for cases where there are less than 16 entries in result array. </p>
  
  <p>In case both of these are not possible, the last solution would be to have it transformed into something like:
  <img src="https://i.stack.imgur.com/aUfmW.png" alt="back_up_output_table"></p>
  
  <p>The reason I want to transform the data is that in most of the cases I would need to find which result array items are appearing and in what order.</p>