Creating a variable and filtering the output

Create a variable to calculate the total price for each item and use it to filter your output.

Procedure

Under the UNNEST clause in the query, use the LET keyword to create a variable and assign to the variable an expression that calculates the total price for an item.
You can do this by multiplying the price element by the qty element: LET $total = toDecimal(qty * price)
Add a total_price element with the value of the $total variable in the SELECT clause.
The SELECT clause should look like this:
```
SELECT {
    c.name,
    c.rating,
    o.orderno,
    itemno,
    total_price = $total
}
```
Under the LET clause, add a WHERE clause to keep the items with a total price above 100: WHERE $total > 1000.
Under the JOIN clause, add another WHERE clause to keep the customers with a rating above 650: WHERE c.rating > 650.
To return an output with only the item with the highest total price, add the following clause after the SELECT clause: ORDER BY $total DESC LIMIT 1.
This orders the output from highest to lowest total, and keeps only the first result.

Results

The full query should look like this:

FROM order AS o
UNNEST o.items
LET $total = toDecimal(qty * price)
WHERE $total > 1000
JOIN customer AS c ON c.custid = o.custid
WHERE c.rating > 650
SELECT {
    c.name,
    c.rating,
    o.orderno,
    itemno,
    total_price = $total
} ORDER BY $total DESC LIMIT 1

It return the following result:

[
	{
		"name": "T. Cruise",
		"rating": 750,
		"orderno": 1002,
		"itemno": 460,
		"total_price": 9594.05
	}
]

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!

Leave your feedback here