Using the GROUP AS clause
The GROUP AS clause is used to create an alias for the groups generated by a GROUP BY clause, in order to reference them later in the query.
The GROUP AS keyword should be followed by an alias which must be unique
throughout the query.
GROUP AS aliasFor example, with the following input
data:
{
"orders":[
{
"customer":"John Smith",
"order_id":"abc-12345",
"items":[
{
"item_id":"97516848-jiargn",
"quantity":2
}
]
},
{
"customer":"Jane Doe",
"order_id":"def-12345",
"items":[
{
"item_id":"97516848-kftesn",
"quantity":3
},
{
"item_id":"96946848-metasb",
"quantity":1
}
]
},
{
"customer":"Jane Doe",
"order_id":"ghi-69875",
"items":[
{
"item_id":"81516886-kfoaen",
"quantity":2
},
{
"item_id":"68946852-oasbsb",
"quantity":4
}
]
}
]
}You can use the GROUP BY clause to group the result by
customer and then use the GROUP AS to create an array of customer_orders elements, which will contain all orders for
the same customer. You can then reference objects from that array in the
query.
FROM orders
GROUP BY customer
GROUP AS customer_orders
SELECT {
customer_orders
}This query returns the following
result:
[
{
"customer_orders": [
{
"customer": "Jane Doe",
"order_id": "def-12345",
"items": [
{
"item_id": "97516848-kftesn",
"quantity": 3
},
{
"item_id": "96946848-metasb",
"quantity": 1
}
]
},
{
"customer": "Jane Doe",
"order_id": "ghi-69875",
"items": [
{
"item_id": "81516886-kfoaen",
"quantity": 2
},
{
"item_id": "68946852-oasbsb",
"quantity": 4
}
]
}
]
},
{
"customer_orders": [
{
"customer": "John Smith",
"order_id": "abc-12345",
"items": [
{
"item_id": "97516848-jiargn",
"quantity": 2
}
]
}
]
}
]