| When you have a table containing an array of RECORDS, you may get confused about how to get its element value. In Google BigQuery, a persistent user-defined function (UDF) is a function stored in your BigQuery project that can be reused across multiple queries, improving performance by avoiding repeated compilation and potentially reducing execution time for frequently used custom logic. To create one, you use the CREATE FUNCTION statement specifying the function's name, arguments, return type, and the SQL code defining its behavior, using the PERSISTENT keyword; the function's code is then stored within your project and can be called from various queries using its name.
When you have nested_fields from Apache Kafka composed of key and value, the first action you should take is to create a function for better access. Below query is creating a function CK_GET_VAL(key_string, arr): CREATE OR REPLACE FUNCTION `{project_id}.{dataset_id}.CK_GET_VAL`(string_key STRING, arr ARRAY<STRUCT<key STRING, value STRING>>)
RETURNS STRING
AS (
(SELECT COALESCE((SELECT value FROM UNNEST(arr) WHERE key = string_key LIMIT 1), NULL))
);
Once the function is created successfully, you can get the value stored in nested_fields like below: SELECT
dt,
`{project_id}.{dataset_id}.CK_GET_VAL`('cid', nested_fields) AS client_id, -- Backticks here
`{project_id}.{dataset_id}.CK_GET_VAL`('t', nested_fields) AS event_type, -- And here
`{project_id}.{dataset_id}.CK_GET_VAL`('m', nested_fields) AS membership, -- And here
`{project_id}.{dataset_id}.CK_GET_VAL`('ep', nested_fields) AS episode_slug, -- And here
FROM
`{project_id}.{dataset_id}t.kafka_raw` -- Backticks here
WHERE dt BETWEEN '2025-01-01 00:18:00+00' AND '2025-01-02 00:33:00+00'
LIMIT 100
If you want to check if CK_GET_VAL() is defined, you can check through BigQuery like below: SELECT COUNT(*)
FROM `{project_id}.{dataset_id}.INFORMATION_SCHEMA.ROUTINES`
WHERE ROUTINE_NAME = '{function_id}'
| 
