title: BigQuery UPDATE nested array field
body: <p>I need to update nested field in one table, using the value from another table.
  Using <a href="https://stackoverflow.com/questions/44953369/update-nested-field-in-bigquery-table">this solution</a> i came up with something that works, but not exactly as i want it to. 
  Here is my solution:</p>
  
  <p><div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
  <div class="snippet-code">
  <pre class="snippet-code-js lang-js prettyprint-override"><code>#standardSQL
  UPDATE
    `attribution.daily_sessions_20180301_copy1` AS target
  SET
  hits = ARRAY(
    SELECT AS STRUCT * REPLACE(ARRAY(
      SELECT AS STRUCT *
      FROM(
        SELECT AS STRUCT * REPLACE(map.category AS productCategoryAttribute) FROM UNNEST(product))) AS product) FROM UNNEST(hits)
  )
  FROM
    `attribution.attribute_category_map`
  AS map
  WHERE
    (
      SELECT REPLACE(LOWER(prod.productCategory), 'amp;', '') FROM UNNEST(target.hits) AS h,
      UNNEST(h.product) AS prod LIMIT 1) = map.raw_name</code></pre>
  </div>
  </div>
  </p>
  
  <p>attribute_category_map is a table with two columns where i look for corresponding value in column 1 and replace data in target table with value from column 2. Best result i achieved - updated all nested fields on one row with the same value, wich is only correct for the first nested field, instead of updating each nested field with specific value.</p>
  
  <p>Simplified schema of the main table:</p>
  
  <pre><code>[  
     {  
        "name":"sessionId",
        "type":"STRING",
        "mode":"NULLABLE"
     },
     {  
        "name":"hits",
        "type":"RECORD",
        "mode":"REPEATED",
        "fields":[  
           {  
              "name":"product",
              "type":"RECORD",
              "mode":"REPEATED",
              "fields":[  
                 {  
                    "name":"productCategory",
                    "type":"STRING",
                    "mode":"NULLABLE"
                 },
                 {  
                    "name":"productCategoryAttribute",
                    "type":"STRING",
                    "mode":"NULLABLE"
                 }
              ]
           }
        ]
     }
  ]
  </code></pre>
  
  <p>There are usually several hits within session row and several products within one hit. With values looking like those (if you unnest):</p>
  
  <pre><code>-----------------------------------------------------------------------------
  sessionId | hits.product.productCategory| hit.product.productCategoryAttribute
  -----------------------------------------------------------------------------
  1         | automotive chemicals        | null
  1         | automotive tools            | null
  1         | null                        | null
  2         | null                        | null
  2         | automotive chemicals        | null
  2         | null                        | null
  3         | null                        | null
  3         | bed accessories             | null
  4         | null                        | null
  4         | null                        | null
  4         | automotive chemicals        | null
  4         | null                        | null
  -----------------------------------------------------------------------------
  </code></pre>
  
  <p>Schema of the map table:</p>
  
  <pre><code>[  
     {  
        "name":"raw_name",
        "type":"STRING",
        "mode":"NULLABLE"
     },
     {  
        "name":"category",
        "type":"STRING",
        "mode":"NULLABLE"
     }
  ]
  </code></pre>
  
  <p>with values like this:</p>
  
  <pre><code>---------------------------------------------------
  raw_name              |category                   |
  ---------------------------------------------------
  automotive chemicals  |d1y2 - automotive chemicals|
  automotive paint      |dijf1 - automotive paint   |
  automotive tools      |efw1 - automotive tools    |
  baby &amp; infant toys    |wwfw - baby &amp; infant toys  |
  batteries &amp; power     |fdsv- batteries &amp; power    |
  bed accessories       |0k77 - bed accessories     |
  bike racks            |12df - bike racks          |
  --------------------------------------------------
  </code></pre>
  
  <p>What i want as a result:</p>
  
  <pre><code>-----------------------------------------------------------------------------
      sessionId | hits.product.productCategory| hit.product.productCategoryAttribute
  -----------------------------------------------------------------------------
      1         | automotive chemicals        | d1y2 - automotive chemicals
      1         | automotive tools            | efw1 - automotive tools
      1         | null                        | null
      2         | null                        | null
      2         | automotive chemicals        | d1y2 - automotive chemicals
      2         | null                        | null
      3         | null                        | null
      3         | bed accessories             | 0k77 - bed accessories
      4         | null                        | null
      4         | null                        | null
      4         | automotive chemicals        | d1y2 - automotive chemicals
      4         | null                        | null
      -----------------------------------------------------------------------------
  </code></pre>
  
  <p>I need to take value productCategory from main table, look it up in map table in column raw_name, take value from colum category and put it to productCategoryAttribute column of main table. Main problem is target fields are double nested and i can't figure out how to join directly to them. </p>