Skip to main content

Using the UNION ALL clause

The UNION ALL clause allows you to build a single array based on the results of multiple query blocks or sub-queries.

In Talend Data Mapper with the 8.0.1-R2024-05 monthly update or a later one, you can also use UNION ALL ENTRY to create an array of record with key/value pairs from multiple sub-queries.

A query with a UNION ALL clause to combine the results of sub-queries in an array should be formatted as follows:
SELECT [
  <sub-query>
   UNION ALL
  <sub-query>
]
This syntax is used in the following example: Merging two arrays based on conditions.
A query with a UNION ALL ENTRY clause to combine the results of sub-queries in an array of records should be formatted as follows:
SELECT [
  { key = '<name_of_key>', value = <value_expression> }
  UNION ALL ENTRY
  { key = '<name_of_key>', value = <value_expression> }
]
For example:
FROM customer
SELECT [
  { key = 'id', value = custid }
  UNION ALL ENTRY
  { key = 'name', value = name }
  UNION ALL ENTRY
  { key = 'address', value = concatWith(', ', address.street, address.city) }
]
A query with a UNION ALL clause to combine the results of query blocks in an array should be formatted as follows:
<query_block>
UNION ALL
<query_block>
You can use conditions in a UNION ALL clause. For example:
FROM order
LET $items_size = size(items)
WHERE $items_size > 2
SELECT [
  IF ($items_size > 2) ( FROM items INDEX $i SELECT ENTRY { key = concat('itemno_', $i + 1), value = itemno } )
  UNION ALL ENTRY
  IF ($items_size > 2) ( FROM items INDEX $j SELECT ENTRY { key = concat('qty_', $j + 1), value = qty } )
  UNION ALL ENTRY
  IF ($items_size > 2) ( FROM items INDEX $k SELECT ENTRY { key = concat('price_', $k + 1), value = price } )
]

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 – let us know how we can improve!