sql - BigQuery UPDATE nested array field

Question

I need to update nested field in one table, using the value from another table. Using this solution i came up with something that works, but not exactly as i want it to. Here is my solution:

#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

Answer 1

Waitting for answers