Aggregate

Aggregate combining the groupby= query parameter and the function calling capabilities of the select= query parameter.

Below are a few examples of the url structure and shape of the response

Call an aggregate function

curl "http://localhost:3000/users_projects?select=user_id,total:$count(project_id)&groupby=user_id&order=user_id.asc"

[
    {"user_id":1,"total":2},
    {"user_id":2,"total":2},
    {"user_id":3,"total":3}
]

Call an aggregate function with groupby

curl "http://localhost:3000/product_orders?select=city,total_order_amount:$sum(order_amount)&groupby=city&order=city"

[
    {"city":"Arlington","total_order_amount":"$37,000.00"},
    {"city":"GuildFord","total_order_amount":"$50,500.00"},
    {"city":"Shalford","total_order_amount":"$13,000.00"}
]

Call a window function with partition

curl "http://localhost:3000/product_orders?select=order_id,city,order_amount,grand_total:$sum(order_amount)-p(city)"

[
    {"order_id":1002,"city":"Arlington","order_amount":"$20,000.00","grand_total":"$37,000.00"},
    {"order_id":1007,"city":"Arlington","order_amount":"$15,000.00","grand_total":"$37,000.00"},
    {"order_id":1008,"city":"Arlington","order_amount":"$2,000.00","grand_total":"$37,000.00"},
    {"order_id":1010,"city":"GuildFord","order_amount":"$500.00","grand_total":"$50,500.00"},
    {"order_id":1004,"city":"GuildFord","order_amount":"$15,000.00","grand_total":"$50,500.00"},
    {"order_id":1006,"city":"GuildFord","order_amount":"$25,000.00","grand_total":"$50,500.00"},
    {"order_id":1001,"city":"GuildFord","order_amount":"$10,000.00","grand_total":"$50,500.00"},
    {"order_id":1003,"city":"Shalford","order_amount":"$5,000.00","grand_total":"$13,000.00"},
    {"order_id":1009,"city":"Shalford","order_amount":"$1,000.00","grand_total":"$13,000.00"},
    {"order_id":1005,"city":"Shalford","order_amount":"$7,000.00","grand_total":"$13,000.00"}
]

Call a window function with order

curl "http://localhost:3000/product_orders?select=order_id,customer_name,city,order_amount,rank:$rank()-o(order_amount.desc)"

[
    {"order_id":1006,"customer_name":"Paum Smith","city":"GuildFord","order_amount":"$25,000.00","rank":1},
    {"order_id":1002,"customer_name":"David Jones","city":"Arlington","order_amount":"$20,000.00","rank":2},
    {"order_id":1007,"customer_name":"Andrew Smith","city":"Arlington","order_amount":"$15,000.00","rank":3},
    {"order_id":1004,"customer_name":"Michael Smith","city":"GuildFord","order_amount":"$15,000.00","rank":3},
    {"order_id":1001,"customer_name":"David Smith","city":"GuildFord","order_amount":"$10,000.00","rank":5},
    {"order_id":1005,"customer_name":"David Williams","city":"Shalford","order_amount":"$7,000.00","rank":6},
    {"order_id":1003,"customer_name":"John Smith","city":"Shalford","order_amount":"$5,000.00","rank":7},
    {"order_id":1008,"customer_name":"David Brown","city":"Arlington","order_amount":"$2,000.00","rank":8},
    {"order_id":1009,"customer_name":"Robert Smith","city":"Shalford","order_amount":"$1,000.00","rank":9},
    {"order_id":1010,"customer_name":"Peter Smith","city":"GuildFord","order_amount":"$500.00","rank":10}
]

Call an window function with partition and order

GET /product_orders?select=order_id,customer_name,city,order_amount,row_number:$row_number()-p(city)-o(order_amount.desc)

curl "http://localhost:3000/product_orders?select=order_id,customer_name,city,order_amount,row_number:$row_number()-p(city)-o(order_amount.desc)"

[
    {"order_id":1002,"customer_name":"David Jones","city":"Arlington","order_amount":"$20,000.00","row_number":1},
    {"order_id":1007,"customer_name":"Andrew Smith","city":"Arlington","order_amount":"$15,000.00","row_number":2},
    {"order_id":1008,"customer_name":"David Brown","city":"Arlington","order_amount":"$2,000.00","row_number":3},
    {"order_id":1006,"customer_name":"Paum Smith","city":"GuildFord","order_amount":"$25,000.00","row_number":1},
    {"order_id":1004,"customer_name":"Michael Smith","city":"GuildFord","order_amount":"$15,000.00","row_number":2},
    {"order_id":1001,"customer_name":"David Smith","city":"GuildFord","order_amount":"$10,000.00","row_number":3},
    {"order_id":1010,"customer_name":"Peter Smith","city":"GuildFord","order_amount":"$500.00","row_number":4},
    {"order_id":1005,"customer_name":"David Williams","city":"Shalford","order_amount":"$7,000.00","row_number":1},
    {"order_id":1003,"customer_name":"John Smith","city":"Shalford","order_amount":"$5,000.00","row_number":2},
    {"order_id":1009,"customer_name":"Robert Smith","city":"Shalford","order_amount":"$1,000.00","row_number":3}
]