Writing Nested Query Expressions in Ballerina
After reading my previous blog post you guys must have been wondering what other cool stuff can be done using query expression in Ballerina! Isn’t it? So, I thought of writing to you about how inner queries can be handled graciously using query expression in Ballerina.
Similar to SQL, a nested query expression or an inner query expression is a query expression within another query expression. An inner query expression can be embedded within a from clause or a join clause where its resultant value set can be utilized in the outer query expression. You can use any query clause in the inner query as well.
Let’s see how we can use nested queries with an example.
I have two CSV files with order data and customer data. Order data contain information about the Order No, Customer Name, Order Date, Shipment Date, Item No, Description, Quantity, Outstanding Quantity, and Outstanding Orders of a specific customer order. Customer data contain information about the Customer Id, Customer Name, and Country of a specific customer. The datasets are available in https://github.com/lasinicl/datasets.
Let’s get the result for the following queries using nested query expression.
1. Get the orders with highest five order quantities of customers based in the USA.
getOrderData() and getCustomerData() methods are used to read the data from the CSV files and add them to two lists containing Order type members and Customer type members respectively.
The from clause iterates the orders list. The query expression embedded in the join clause produces a list of customers based in the USA thereby letting us join the resultant customer list with the orders list. The join-on-condition is used to specify the expressions used to join each value. In this example customerName is used to join each order data value with a customer data value. In order to get the five orders with the highest order quantity, we need to order the result set using the order quantity in descending order and limit the number of results to 5. The select clause gives you the final list of orders.
The result of the above query expression is
{"orderNo":1208,"customerName":"AIRBUS OPERATIONS GMBH","orderDate":"5/20/2014","shipmentDate":"7/3/2014","itemNo":"2408","description":"L/JKT 102MK3 AD/CH Faa 10yr","quantity":225,"outstandingQuantity":225,"outstandingOrders":0.0}
{"orderNo":1207,"customerName":"AIRBUS OPERATIONS GMBH","orderDate":"5/20/2014","shipmentDate":"7/3/2014","itemNo":"2123","description":"L/JKT 102Mk2BA CREW VALTamp BA","quantity":110,"outstandingQuantity":110,"outstandingOrders":0.0}
{"orderNo":1208,"customerName":"AIRBUS OPERATIONS GMBH","orderDate":"5/20/2014","shipmentDate":"7/3/2014","itemNo":"2123","description":"L/JKT 102Mk2BA CREW VALTamp BA","quantity":85,"outstandingQuantity":85,"outstandingOrders":0.0}
{"orderNo":1171,"customerName":"AIRBUS OPERATIONS GMBH","orderDate":"5/20/2014","shipmentDate":"7/2/2014","itemNo":"2123","description":"L/JKT 102Mk2BA CREW VALTamp BA","quantity":75,"outstandingQuantity":75,"outstandingOrders":3187.5}
{"orderNo":1170,"customerName":"AIRBUS OPERATIONS GMBH","orderDate":"5/20/2014","shipmentDate":"6/19/2014","itemNo":"2136","description":"L/JKT 102MK2BA PASS +VAL STD","quantity":60,"outstandingQuantity":60,"outstandingOrders":2550.0}Nested from clauses can also be used to get the results for the above query.
But since the join clause internally uses a hash table to join the relevant values, using thejoin clause rather than using nested from clauses is more efficient.
2. Get the orders with highest five order quantities of customers based in the USA and having more than 1000 outstanding orders.
The query expression embedded in thefrom clause will filter out the orders with more than 1000 outstanding orders. Then those order details will be joined with the customers based in USA. That result set is ordered using the order quantity and the orders with highest quantity will be output from the query expression.
{"orderNo":1171,"customerName":"AIRBUS OPERATIONS GMBH","orderDate":"5/20/2014","shipmentDate":"7/2/2014","itemNo":"2123","description":"L/JKT 102Mk2BA CREW VALTamp BA","quantity":75,"outstandingQuantity":75,"outstandingOrders":3187.5}
{"orderNo":1170,"customerName":"AIRBUS OPERATIONS GMBH","orderDate":"5/20/2014","shipmentDate":"6/19/2014","itemNo":"2136","description":"L/JKT 102MK2BA PASS +VAL STD","quantity":60,"outstandingQuantity":60,"outstandingOrders":2550.0}
{"orderNo":1051,"customerName":"DAECO LTD","orderDate":"1/20/2014","shipmentDate":"1/24/2014","itemNo":"6096009","description":"BATTERY SEACELL L8S","quantity":50,"outstandingQuantity":50,"outstandingOrders":2772.27}
{"orderNo":1169,"customerName":"AIRBUS OPERATIONS GMBH","orderDate":"5/20/2014","shipmentDate":"6/11/2014","itemNo":"2136","description":"L/JKT 102MK2BA PASS +VAL STD","quantity":40,"outstandingQuantity":40,"outstandingOrders":1400.0}
{"orderNo":1168,"customerName":"AIRBUS OPERATIONS GMBH","orderDate":"5/20/2014","shipmentDate":"7/2/2014","itemNo":"A346600A00","description":"COT SURVIVAL Mk4","quantity":8,"outstandingQuantity":8,"outstandingOrders":2360.0}The above query can be answered by using the following query expression too, where the query expression embedded in the from clause outputs the results as a stream and query expression embedded in the join clause outputs the results as a table.
Whether the results set of the inner query expression is a stream, table or list of the expected type defined the join clause or from clause will not change the expected set of results from the outer query expression.
And that’t pretty much it!! More complex queries can easily be answered using Ballerina nestsed query expressions making your life easy. If you have any questions feel free to leave a comment.
Till we meet again, Adiós!!
