BigQuery nested table UPDATE based on condition of non-nested data AND nested data

I am trying to update records in a bigQuery database that looks like this:

Using the code below:

UPDATE `tottus-chile.espacio.nested_table`
SET addresses =
  ARRAY(
    SELECT AS STRUCT * REPLACE('sleep' AS 
       address)
    FROM UNNEST(addresses)
  )
WHERE first_name="pauli shore" AND EXISTS (
  SELECT 1 FROM UNNEST(addresses) AS h 
  WHERE h.status="previous"
)

However, as show in the picture, it makes ALL of the addresses read “sleep” instead of just the “previous one.

I must admit, I’m pretty confused as to how best to deal with alterations within nested tables. Is there a general way to achieve just updating the “previous” piece of the nested array?

Answer

Is this what you want to do?

UPDATE `tottus-chile.espacio.nested_table`
    SET addresses =
        ARRAY(SELECT AS STRUCT a.* EXCEPT (address),
                               (CASE WHEN a.status = 'previous' THEN 'sleep' ELSE a.address END) as address
              FROM UNNEST(addresses) a
             )
WHERE first_name = 'pauli shore' AND
      EXISTS (SELECT 1
              FROM UNNEST(addresses) AS h 
              WHERE h.status = 'previous'
             );

This does rearrange the columns in the struct — which can be fixed with more coding. I think this might be what you want to do.

EDIT:

To avoid shifting, you can explicitly list the columns:

UPDATE `tottus-chile.espacio.nested_table`
    SET addresses =
        ARRAY(SELECT AS STRUCT a.status,
                               (CASE WHEN a.status = 'previous' THEN 'sleep' ELSE a.address END) as address,
                               a.* EXCEPT (status, address)
              FROM UNNEST(addresses) a
             )
WHERE first_name = 'pauli shore' AND
      EXISTS (SELECT 1
              FROM UNNEST(addresses) AS h 
              WHERE h.status = 'previous'
             );

This just reconstructs the struct but conditionally reassigning the value for address.