Filter array of rows with indexes

This guide explains different methods to efficiently filter arrays of rows based on field values.

Simple Filtering with TRANSFORM()

The simplest approach is to use TRANSFORM(), though it's not always the most efficient:

Limitation: This method performs a full scan of the array for each search operation, which can be inefficient for large datasets or frequent searches.

Optimizing with Indexes

For scenarios requiring multiple searches, creating an index structure can significantly improve performance.

For Unique Values: INDEX_UNIQUE()

When the search field contains unique values:

For Non-Unique Values: INDEX_MULTI()

When the search field may contain duplicate values:

Technical Details

The index functions return different data structures:

• INDEX_UNIQUE() returns MAP(<field_type>, BIGINT)
• INDEX_MULTI() returns MAP(<field_type>, ARRAY(BIGINT))

In both cases, the BIGINT values represent the positions of the matching rows in the original array.