Using the GROUP AS clause - Cloud - 8.0

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