Skip to main content

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

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 – please let us know!