Using the FROM clause

The FROM clause is used to iterate over an input element defined by an identifier.

The FROM clause should be structured as follows:

FROM expression

Note: The FROM clause can be used with an expression that returns an array, a record or a primitive type. With a record or a primitive type, the clause creates an array containing a single item.

In Talend Data Mapper with the 8.0.1-R2024-11 monthly update or a later one, you can use more than one collection after the FROM clause. For example, the FROM customer, order clause is the Cartesian product of customer and order. If the second input array is a direct child of the first input array, for example the FROM order AS o, o.items AS i, the unnesting of the second input array is performed, which is equivalent to FROM order as O UNNEST o.items AS i.

The FROM clause can contain other keywords, such as AS and INDEX.

The AS keyword allows you to create an alias for an array, it can then be used to refer to the elements in the array in subsequent clauses. The alias must be unique throughout the query and should be defined as follows:

FROM expression AS alias

When using a FROM clause without an alias, all identifiers used in the query are treated as relative to the identifier in the FROM clause, except when it is followed by an UNNEST clause or when the identifier is an absolute path. For example, in the following query:

FROM customers
SELECT {
	name,
	id
}

The elements selected are customers.name and customers.id. You would get the same result with this query, where the input data is named input:

FROM customers
SELECT {
	input.customers.name,
	input.customers.id
}

The INDEX keyword allows you to store the index of the current iteration in a variable. The variable must start with the $ character and cannot be a hierarchical identifier, for example, $item.index is not a valid variable. The variable must be unique throughout the query. The INDEX should be defined as follows:

FROM expression INDEX $variable_name

In Talend Data Mapper with the 8.0.1-R2025-11 monthly release, you can use the RECURSIVE keyword in the FROM clause to handle recursive to flat transformation:

FROM RECURSIVE path: recursively iterates over a collection starting at the specified path
FROM path_1 RECURSIVE ON path_2: starts the recursion at path_1 and continues recursively into path_2

You can also use the DEPTH or PARENT keywords to store the depth or the parent of the nested item.

FROM employees RECURSIVE ON team PARENT $p

The following examples are valid FROM clauses:

FROM customers AS c INDEX $i

FROM 1 TO 5 AS i

FROM toArray(address_1, address_2, address_3) AS addr INDEX $i

FROM ['a', 'e', 'i', 'o', 'u', 'y' ] AS vowels

FROM order AS o, o.items AS i

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – please let us know!

Leave your feedback here