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

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 – let us know how we can improve!

Leave your feedback here