Complex values and types

Nested source data may contain arrays, JSON objects, structs, or maps. Their presence in a connector does not mean every nested expression is supported by the SQL workbench.

Start from the catalog

Inspect the columns and use the qualified connector.dataset.table name. The workbench accepts a defined SQL subset; dynamic table functions and arbitrary JSON or array functions are not part of that interface.

For predictable reporting, expose needed nested fields as ordinary columns in a source view. For example, a prepared bq.sales.orders view could expose customer_id, shipping_country, and amount.

SELECT customer_id, shipping_country, SUM(amount) AS revenue
FROM bq.sales.orders
GROUP BY customer_id, shipping_country
LIMIT 100;

Create that view through your database’s normal administration workflow. The FastBI workbench is read-only and does not execute CREATE VIEW.

Arrays and repeated records

Flatten repeated fields at the source when you need one row per item. Keep the parent key on every flattened row. An order with five items becomes five rows, so summing the original order total afterward would multiply it by five.

Choose a clear contract for the prepared table: one row per order, one row per item, or one row per event. Include an item key if a row represents an item. Test empty arrays and null arrays so that their effect on parent-row retention is understood.

Objects, structs, and maps

Project stable fields into typed columns. If object keys vary, decide whether missing keys mean unknown, not applicable, or malformed data. A text representation of an object is not equivalent to a queryable struct.

When keys are dynamic, a separate key/value table can be easier to query. Join it through an explicit entity key and deduplicate to the intended grain before calculating totals.

Cross-source transfer

FastBI preserves supported scalar types where possible. Arrays, JSON, and other complex external-source values can be transferred as text. Do not assume nested operators still apply after data moves to the combining stage.

A Lake table that already has native complex columns can behave differently from an external column serialized during transfer. Check the result schema instead of inferring the type from how a cell looks.

Safe scalar conversion

For prepared text columns, convert explicitly with the SQL functions and inspect failures.

SELECT order_id,
       SAFE_NUMBER(revenue_text) AS revenue,
       SAFE_DATE(order_date_text) AS order_date
FROM lake.uploads.imported_orders
LIMIT 100;

A null conversion result can mean either an absent input or an invalid value. Keep the original column while diagnosing data quality. See results, types, and limits for precision and transfer rules.