CODEWITHSUNDEEP
sqlkql
Syzorr
Syzorr

Reputation: 607

Summarizing a dynamic array after merging with another table in KQL

This is something I'm trying to figure out within KQL - in this example it is calculating the total cost of the order for each customer.

Customer

Name Order
Bob [1, 2, 3]
Kate [1, 3]

Items

ID Name Price
1 Apple 1.00
2 Orange 1.50
3 Banana 1.75

All I have got so far is that if I want to calculate the cost of Bob's order I might do something like

let order = Customer
| where Name == "Bob"
| project Order;
Items
| where ID in (order)
| summarize total_cost = sum(Price)

However if I wanted to do this for Bob and Kate, and any number of other "customers" I have no idea of how to get to this end goal.

Any advice would be greatly appreciated! Even though this is in KQL specifically, even knowing how to do it in SQL would allow me to hopefully translate it better.

Upvotes: 1

Views: 670

Answers (1)

Yoni L.
Yoni L.

Reputation: 25895

you could try this:

  • start with mv-expanding the array of orders.
  • continue with a lookup of the Price in the Items table.
  • finish with a summarize of the sum() of Price by Name.
let Customer = datatable(Name:string, Order:dynamic) [
    'Bob', dynamic([1, 2, 3]),
    'Kate', dynamic([1, 3]),
]
;
let Items = datatable(ID:long, Name:string, Price:double) [
    1, 'Apple', 1.00,
    2, 'Orange', 1.50,
    3, 'Banana', 1.75,
]
;
Customer
| mv-expand Order to typeof(long)
| lookup Items on $left.Order == $right.ID
| summarize sum(Price) by Name
Name sum_Price
Bob 4.25
Kate 2.75

Upvotes: 1

Related Questions