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

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