Add fields

The ADD_FIELDS operation can add multiple fields to elements of an array of rows based on foreign key relationships. This is similar to joining tables with denormalized arrays in SQL but operates at the column level.

Syntax

ADD_FIELDS(
    <feature1>, <feature2>
    TO <feature_of_type_array_of_rows>
    BINDING
        FIELDS [... list of fields]
        WITH [... list of features]
)
sql

Where:

  • Each source_column is a column containing values you want to copy
  • Each alias is the name for the added field in the output row
  • array_of_rows is a column containing arrays of rows with foreign keys that reference the primary keys in the source columns' tables

To be valid, <feature1>, <feature2>... need to be fully defined by the [... list of features] binding.

Example

Let's look at a common scenario where we have orders linked to stores through a store ID.

This query:

  1. Defines two entities: ORDERS and STORES
  2. Creates sample data for orders and stores using SOURCE_BATCH
  3. Uses ADD_FIELDS to copy the store category to each order based on the store ID

Key points

  • The foreign key relationship semantic is enforced by the type system (ARRAY(BIGINT#ORDERS))
  • The operation maintains referential integrity for each array element

Add fields to rows in array of rows

Key points:

  • Multiple fields can be added to each row in the array in a single operation
  • The foreign key relationships are enforced by the type system (BIGINT#ORDERS, BIGINT#STORES)

Using automatic binding

Suggest changes to this page
Last update at: 2025/10/13 10:23:46