Search code examples
structgoogle-bigquerybigquery-udf

BigQuery UDF remove from array. How do I anonymously reference an element in a struct?


This removes the elements from the array, but it renames the field to v.

Is there a better way to do this? OR Can I get the first element of the struct (ex. t[0])?

    CREATE TEMP FUNCTION remove(arr ANY TYPE, val ANY TYPE) AS ((
      SELECT ARRAY_AGG(t)
      FROM (SELECT * FROM UNNEST(arr) v) t
      WHERE t.v <> val
    ));

Solution

  • Is there a better way to do this?

    Use below trimmed down version

    CREATE TEMP FUNCTION remove(arr ANY TYPE, val ANY TYPE) AS ((
      SELECT ARRAY_AGG(v) 
      FROM UNNEST(arr) v
      WHERE v <> val
    ));