title: How do you use multiple values from UNNEST in WHERE clause of a subquery?
body: <p>Using Standard SQL on BigQuery, I am trying to select  only certain values from a column, dependant on what values are stored inside an array on the same record.</p>
  
  <p>For example, each record contains the event_timestamp column, and an array of user properties. I would like to select only the event_timestamps for the events where a specific user property is met, e.g. key = 'Level_Number' and value.string_value = '04'; but I need to do this for multiple user properties in the same table, e.g. timestamps where value.string_value = '04' in one field and then where value.string_value ='05' in the next.</p>
  
  <p>The problem is that when I try to do this I am getting the Error: "Scalar subquery produced more than one element".</p>
  
  <p>For example, these are queries which I have tried:</p>
  
  <pre><code>SELECT
  user_pseudo_id AS user_id,
  (SELECT TIMESTAMP_MICROS(event_timestamp) from `analytics_161693185.events_*`
    WHERE EXISTS 
        (SELECT value.string_value FROM UNNEST (user_properties) WHERE key = "Level_Number" AND value.string_value = '02'))  AS time_turned_02,   
  FROM `analytics_161693185.events_*`
  </code></pre>
  
  <pre><code>SELECT
  user_pseudo_id AS user_id,
  (SELECT TIMESTAMP_MICROS(event_timestamp) from `analytics_161693185.events_*`
    WHERE
        (SELECT value.string_value FROM UNNEST (user_properties) WHERE key = "Level_Number") = '02')  AS time_turned_02,   
  FROM `analytics_161693185.events_*`
  </code></pre>
  
  <pre><code>SELECT
  user_pseudo_id AS user_id,
  (SELECT TIMESTAMP_MICROS(event_timestamp) from `analytics_161693185.events_*`
    WHERE
        (SELECT key FROM UNNEST (user_properties)) = 'Level_Number'
        AND (SELECT value.string_value FROM UNNEST (user_properties)) = '02') AS time_turned_02,   
  FROM `analytics_161693185.events_*`
  </code></pre>
  
  <p>But all of them give the same error.</p>
  
  <p>I know that one solution is to create temporary tables using code similar to the following:</p>
  
  <pre><code>WITH
  temporary_table_02 AS(
  SELECT
  user_pseudo_id AS user_id,
  TIMESTAMP_MICROS(event_timestamp) AS time_turned_02,
  FROM `analytics_161693185.events_*`
  WHERE (SELECT value.string_value FROM UNNEST(user_properties) WHERE key = "Level_Number") = '02' 
  ),
  </code></pre>
  
  <p>This then allows you to select the values from these tables and join them in a new table, but since there are 18 levels, creating 18 temporary tables results in BigQuery saying the query is too complex, so this solution is not feasible.</p>
  
  <p>Here is a sample of the data if it helps make things clearer:
  <a href="https://i.stack.imgur.com/jQaUj.png" rel="nofollow noreferrer">Data Sample</a></p>
  
  <p>Here is a spreadsheet showing how I would like the Expected Results to look, from a simplified schema. <a href="https://docs.google.com/spreadsheets/d/1QGsp5ko54ZtRIdYejoA3tcJ99PCml6dL7sMRJRdLQzc/edit?usp=sharing" rel="nofollow noreferrer">https://docs.google.com/spreadsheets/d/1QGsp5ko54ZtRIdYejoA3tcJ99PCml6dL7sMRJRdLQzc/edit?usp=sharing</a></p>