Relationship Specification

In an implicit filtering scenario, we wish to specify whether to AND or OR the logical values resulting from applying one or more logical expression(s) to each of a set of columns.

This section is complemented by

  • Column Selectionwhere we cover how to select the columns to each of which we will apply filtering logic.

AND

We wish to filter rows for which a logical expression is TRUE for all of a selected set of columns.

In this example, we wish to return any row in the table dummy_table for which the value of all columns of type INT64 is bigger than 0.

BEGIN
    DECLARE v_table_name STRING;
    DECLARE v_condition STRING;
    DECLARE v_data_type STRING;
    SET v_table_name = 'dummy_table';
    SET v_data_type = 'INT64';
    SET v_condition =
            (SELECT STRING_AGG(CONCAT(column_name, ' > 0 '), 'AND ')
             FROM refcon.examples.INFORMATION_SCHEMA.COLUMNS
             WHERE table_name = v_table_name
               AND data_type = v_data_type);
    EXECUTE IMMEDIATE FORMAT("""SELECT *
                                FROM refcon.examples.%s
                                WHERE %s
                             """, v_table_name, v_condition);
END;

Here is how this works:

  • First, we want to construct a null condition over all columns in the table.
  • INFORMATION_SCHEMA provides metadata for each table in the dataset and COLUMNS view returns the info for each column in the table.
  • We CONCAT the column name with >0 to getcol_1 > 0` and keep only columns of integer type.
  • We use STRING_AGG with AND as a separator to get col_1 > 0 AND col_2 > 0 AND ...
  • We use EXECUTE IMMEDIATE to execute our dynamic query using the v_condition variable.
  • We achieve this using the FORMAT function which formats a data type expression as a string. %s will be replaced with the condition.

OR

We wish to filter rows for which a logical expression is TRUE for any of a selected set of columns.

In this example, we wish to return any row in the table dummy_table for which the value of any columns of type INT64 is bigger than 0.

BEGIN
    DECLARE v_table_name STRING;
    DECLARE v_condition STRING;
    DECLARE v_data_type STRING;
    SET v_table_name = 'dummy_table';
    SET v_data_type = 'INT64';
    SET v_condition =
            (SELECT STRING_AGG(CONCAT(column_name, ' > 0 '), 'OR ')
             FROM refcon.examples.INFORMATION_SCHEMA.COLUMNS
             WHERE table_name = v_table_name
               AND data_type = v_data_type);
    EXECUTE IMMEDIATE FORMAT("""SELECT *
                                FROM refcon.examples.%s
                                WHERE %s
                             """, v_table_name, v_condition);
END;

Here is how it works:

This works similarly to the scenario above, we just use OR instead of AND to separate the conditions.

SQL
I/O