Using the GROUP BY clause
The GROUP BY clause is used to group the results of a previous clause based on an expression.
The GROUP BY clause can be followed by a simple or conditional
expression. You can also define an alias using the AS
keyword:
GROUP BY expression AS identifier In Talend Data Mapper with the
8.0.1-R2024-11 monthly update or a later one, you can use a comma-separated list of multiple keys in the GROUP BY
clause:
GROUP BY expression_1 AS identifier_1, expression_2 AS identifier_2Information noteNote: Note that there is no limit in the number of keys, and each grouping expression
can be aliased.
After grouping, only the grouping expression can be referenced in the current query. Non-grouping elements can be referenced only as arguments in Aggregation functions.
For example, with the following input data:
{
"customers":[
{
"firstName":"John",
"lastName": "Smith",
"orders":[
{
"order_id":"abc-12345",
"items":[
{
"item_id":"97516848-jiargn",
"quantity":2
}
]
}
]
},
{
"firstName":"Jane",
"lastName": "Doe",
"orders":[
{
"order_id":"def-12345",
"items":[
{
"item_id":"97516848-kftesn",
"quantity":3
},
{
"item_id":"96946848-metasb",
"quantity":1
}
]
}
]
}
]
}You can use the GROUP BY clause to group the result by
order_id and use the sum function to get the sum of
all items in the order:
FROM customers UNNEST orders UNNEST items
GROUP BY order_id AS id
SELECT {
id,
total_items = sum(quantity)
}This query returns the following result:
[
{
"id": "abc-12345",
"total_items": 2
},
{
"id": "def-12345",
"total_items": 4
}
]