Using the GROUP BY clause - Cloud

Talend Data Shaping Language Reference Guide

Version
Cloud
Language
English (United States)
Product
Talend Cloud
Module
Data Shaping Language
Content
Design and Development

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

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
	}
]