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