title: How to UNNest multiple arrays in BigQuery?
body: <p>I have this json that is stored in a BigQuery table in 3 fields token, questions, answers</p>
  
  <p>token:STRING, questions:STRING, answers:STRING</p>
  
  <p>Questions and answers are <code>STRING</code> because they are dynamic fields.</p>
  
  <p><strong>token</strong> field has single value.</p>
  
  <p><strong>questions</strong> field has <code>dictionary</code> object with "fields" being <code>list</code> object and has 3 questions.</p>
  
  <p><strong>answers</strong> field is a <code>list</code> object with answers to the 3 questions and <code>id</code> will be used for matching a question to an answer. Below is the JSON download from bigquery</p>
  
  <pre><code>token          questions                                      answers
  18e6d8e445     {"fields": [{"id": "L39FyvUohKDV", "properties": {}, "ref": "d8834652-3acf-4541-8354-1e3dcd716667", "title": "What did you think about the changes?", "type": "short_text"}, {"id": "krs82KgxHwGb", "properties": {}, "ref": "5b6e6796-635b-4595-9404-e81617d4540b", "title": "How useful is this feature turning out to be for you?", "type": "opinion_scale"}, {"id": "lBzHtCuzHFM4", "properties": {}, "ref": "b76be913-19b9-4b8a-b2ac-3fb645a65a5c", "title": "Your email address", "type": "email"}], "id": "SdzXVn", "title": "Google Shopping 5/4/18"}       [{"field": {"id": "L39FyvUohKDV", "type": "short_text"}, "text": "t", "type": "text"}, {"field": {"id": "krs82KgxHwGb", "type": "opinion_scale"}, "number": 10, "type": "number"}, {"email": "t@t.com", "field": {"id": "lBzHtCuzHFM4", "type": "email"}, "type": "email"}]
  949b2c57e3     {"fields": [{"id": "krs82KgxHwGb", "properties": {}, "ref": "5b6e6796-635b-4595-9404-e81617d4540b", "title": "How useful is this feature turning out to be for you?", "type": "opinion_scale"}, {"id": "lBzHtCuzHFM4", "properties": {}, "ref": "b76be913-19b9-4b8a-b2ac-3fb645a65a5c", "title": "Your email address", "type": "email"}, {"id": "L39FyvUohKDV", "properties": {}, "ref": "d8834652-3acf-4541-8354-1e3dcd716667", "title": "What did you think about the changes?", "type": "short_text"}], "id": "SdzXVn", "title": "Google Shopping 5/4/18"}       [{"field": {"id": "krs82KgxHwGb", "type": "opinion_scale"}, "number": 10, "type": "number"}, {"email": "someone@mail.com", "field": {"id": "lBzHtCuzHFM4", "type": "email"}, "type": "email"}, {"field": {"id": "L39FyvUohKDV", "type": "short_text"}, "text": "they were awesome", "type": "text"}]             
  146c49cdd6     {"fields": [{"id": "CxhfK22a3XWE", "properties": {}, "ref": "d8834652-3acf-4541-8354-1e3dcd716667", "title": "What did you think about the changes?", "type": "short_text"}, {"id": "oUZxPRaKjmFr", "properties": {}, "ref": "5b6e6796-635b-4595-9404-e81617d4540b", "title": "How useful is this feature turning out to be for you?", "type": "opinion_scale"}, {"id": "zUIP73oXpLD6", "properties": {}, "ref": "b76be913-19b9-4b8a-b2ac-3fb645a65a5c", "title": "Your email address", "type": "email"}], "id": "kaiAsx", "title": "a - b"}                        [{"field": {"id": "CxhfK22a3XWE", "type": "short_text"}, "text": "nice", "type": "text"}, {"field": {"id": "oUZxPRaKjmFr", "type": "opinion_scale"}, "number": 2, "type": "number"}, {"email": "foo@bar.com", "field": {"id": "zUIP73oXpLD6", "type": "email"}, "type": "email"}]        
  </code></pre>
  
  <p>@mikhail-berlyant provided this query below which gets me pretty close to what I am expecting. The only problem I am having is that I am unable to get the answer.</p>
  
  <pre><code>SELECT distinct token, id, title AS question,
  JSON_EXTRACT_SCALAR(CONCAT('{',a,'}'), '$.type') answer_type
  --REPLACE(REGEXP_EXTRACT(b, r'"type":".+?"\s*,\s*".+?":(.+)'), '"', '') answer
  FROM `v1-dev-main.typeform.responses`,
  UNNEST(REGEXP_EXTRACT_ALL(JSON_EXTRACT(definition, '$.fields'), r'"title":"(.+?)"')) title WITH OFFSET pos1,
  UNNEST(REGEXP_EXTRACT_ALL(JSON_EXTRACT(definition, '$.fields'), r'"id":"(.+?)"')) id WITH OFFSET pos2,
  UNNEST(REGEXP_EXTRACT_ALL(answers, r'"field": {(.+?)}')) a WITH OFFSET pos3
  --UNNEST(REGEXP_EXTRACT_ALL(answers, r'{(.+?),\s*"field":{.+?}')) b WITH OFFSET pos4
  WHERE pos1 = pos2 
  --AND pos3 = pos4 
  AND id = JSON_EXTRACT_SCALAR(CONCAT('{',a,'}'), '$.id')   
  </code></pre>
  
  <p>Here is the result of above query</p>
  
  <pre><code>token                       id             question       answer_type
  146c43c81cd5780839d3cdd6    zUIP73oXpLD6    Your email address  email
  146c493c1cd5780839d3cdd6    oUZxPRaKjmFr    How useful is this feature turning out to be for you?   opinion_scale
  146c493c05d5780839d3cdd6    CxhfK22a3XWE    What did you think about the changes?   short_text
  18e6d8e33df44a1aa451b445    lBzHtCuzHFM4    Your email address  email
  18e6d8e33df44a1aa451b445    L39FyvUohKDV    What did you think about the changes?   short_text
  18e6d0fa014bfa1aa451b445    krs82KgxHwGb    How useful is this feature turning out to be for you?   opinion_scale
  a63b20df691c9a949b2c57e3    krs82KgxHwGb    How useful is this feature turning out to be for you?   opinion_scale
  a63b20df691c9a949b2c57e3    lBzHtCuzHFM4    Your email address  email
  a63b258ce0339a949b2c57e3    L39FyvUohKDV    What did you think about the changes?   short_text
  </code></pre>
  
  <p>Now, I am just missing the answer.</p>