What should be the type of result from select query in Google BigQuery?

Question

For some reason I need to save the result from EXECUTE IMMEDIATE and reuse it in another query as CTE. Since EXECUTE IMMEDIATE cannot present in CTE, storing it as variable seems to be the last sort.

What should I put for item type of array? The schema of my_ga4.analytics_9999.events_20220101 is just a normal GA4 table.

Schema can be found here: https://support.google.com/analytics/answer/7029846

    DECLARE event_example ARRAY<???>; -- Here

    EXECUTE IMMEDIATE FORMAT(
        "%s", (
            SELECT platform, event_name, event_params FROM `my_ga4.analytics_9999.events_20220101` LIMIT 10
        )
    ) INTO event_example;

I have tried

    DECLARE event_example ARRAY<
        STRUCT<
            platform STRING,
            event_name STRING,
            event_params ARRAY<
                STRUCT<
                    key STRING,
                    value STRUCT<
                        string_value STRING,
                        int_value    INT64,
                        double_value FLOAT64,
                        float_value  FLOAT64
                    >
                >
            >
        >
    >;

Storing the result of the query in a temporary table not work for you? — Damião Martins, Jun 07 '22 at 14:09

score 0 · Accepted Answer · answered Jun 08 '22 at 03:44

Here is the complete solution fo my case.

    DECLARE event_example ARRAY<
        STRUCT<
            result STRUCT<
                platform STRING,
                event_name STRING,
                event_params ARRAY<
                    STRUCT<
                        key STRING,
                        value STRUCT<
                            string_value STRING,
                            int_value    INT64,
                            double_value FLOAT64,
                            float_value  FLOAT64
                        >
                    >
                >
            >
        >
    >;

    EXECUTE IMMEDIATE FORMAT(
        "%s", (
            WITH
                payload AS (
                    SELECT
                        event_name, platform
                    FROM `my_ga4.analytics_9999.events_20220101`
                    GROUP BY
                        event_name, platform
                )
            SELECT
                'SELECT ARRAY_AGG(result) AS result FROM (' ||
                STRING_AGG(
                        '(SELECT STRUCT(platform, event_name, event_params) FROM `my_ga4.analytics_9999.events_20220101` WHERE event_name = \'' ||
                        event_name
                        || '\' AND platform = \'' ||
                        platform
                        || '\' LIMIT 1)', ' UNION ALL '
                    )
                || ') result'
            FROM payload
        )
        ) INTO event_example;

SELECT event_example;

What should be the type of result from select query in Google BigQuery?

1 Answers1