BigQuery stored for loop as Array

Question

I want the result that concatenates items generate by for loop to store in ARRAY or String

Here is my code that can generate list of string that I want but I don't know how to use this list since I cannot store it as Array or Concat it

FOR record IN (SELECT column_name FROM mytable where table_name = 'X')
DO
  SELECT FORMAT("(%s IS NULL OR TRIM(%s) = '')", record.column_name,record.column_name);
END FOR;

I want use it for stored procedure once it call it can generate String that check logic base on columns in table that parse parameter into created procedure

To sum up, I would like to know how to store result of this query as Array or String

Answer 1

You can declare a variable to store the result of the script you wrote. I'm not sure I understood the requirement, but if you just want to store formatted strings as an array, you don't need to use a loop and following query would be enough. You can refer the variable in your stored procedure afterward.

DECLARE formats ARRAY<STRING>;

-- a temp table for the test
CREATE TEMP TABLE mytable AS
SELECT 'col1' AS column_name, 'X' AS table_name
 UNION ALL
SELECT 'col2' AS column_name, 'Y' AS table_name
 UNION ALL
SELECT 'col3' AS column_name, 'X' AS table_name
;

SET formats = (
  SELECT ARRAY_AGG(FORMAT("(%s IS NULL OR TRIM(%s) = '')", column_name, column_name)) 
    FROM mytable
   WHERE table_name = 'X'
);

SELECT formats;


--
output:
[(col3 IS NULL OR TRIM(col3) = ''), (col1 IS NULL OR TRIM(col1) = '')]

And the above condtion can be written in another way.

(col3 IS NULL OR TRIM(col3) = '')

-->

IFNULL(col3, '') = ''

The latter one doesn't need an OR operator and parentheses.