JAQL Data Management

JAQL is a JSON query language similar to SQL. One key difference is the dataset is accessed more like objects. A great overview is found here:JaqlOverview the strength of JAQL is it allows users simple, extendable code to manipulate data that is in a non-proprietary, readable, and commonly used file format. It is primarily used when processing data with Hadoop. JAQL tends to crash when it has exceptions so I would copy-paste my command from an editor. Also never press the up arrow.

Example Data:  
Customer
CustomerID Name Age
1 Joe 23
2 Mika 45
3 Lin 34
4 Sara 56
5 Susan 18
PurchaseOrder
POID CustomerID Purchase
1 3 Fiction
2 1 Biography
3 1 Fiction
4 2 Biography
5 3 Fiction
6 4 Fiction

 

Create Data
$Customer = [ {CustomerId:1, name: Joe , age: 23}
, {CustomerId:1, name: Mika , age: 45}
, {CustomerId:1, name: Lin , age: 34}
, {CustomerId:1, name: Sara , age: 56}
, {CustomerId:1, name: Susan , age: 18}];

$PurchaseOrder = [
{POID: 1, CustomerId:3, Purchase: Fiction }
{POID: 2, CustomerId:1, Purchase: Biography }
{POID: 3, CustomerId:1, Purchase: Fiction }
{POID: 4, CustomerId:2, Purchase: Biography }
{POID: 5, CustomerId:3, Purchase: Fiction }
{POID: 6, CustomerId:3, Purchase: Fiction } ]; 
SELECT
hdfs($Customers) ; 
hdfs($PurchaseOrder) ;
CustomerID Name Age
1 Joe 23
2 Mika 45
3 Lin 34
4 Sara 56
5 Susan 18

Sort BY

$Customers -> sort by [$.age];
CustomerID Name Age
5 Susan 18
1 Joe 23
3 Lin 34
2 Mika 45
4 Sara 56
Filter
$Customers -> filter $.age == 18 ;
CustomerID Name Age
5 Susan 18
INNER JOIN
join $Customers, $PurchaseOrder where $Customers.CustomerId == $PurchaseOrder.CustomerId into {$Customers.name, $PurchaseOrder.*} ; 
CustomerID Name Age POID Purchase
1 Joe 23 2 Biography
1 Joe 23 3 Fiction
2 Mika 45 4 Biography
3 Lin 34 1 Fiction
3 Lin 34 5 Fiction
4 Sara 56 6 Fiction
LEFT OUTER JOIN

join preserve $Customers, $PurchaseOrder where $Customers.CustomerId == $PurchaseOrder.CustomerId into {$Customers.name, $PurchaseOrder.*} ;

CustomerID Name Age POID Purchase
1 Joe 23 2 Biography
1 Joe 23 3 Fiction
2 Mika 45 4 Biography
3 Lin 34 1 Fiction
3 Lin 34 5 Fiction
4 Sara 56 6 Fiction
5 Susan 18 NULL NULL
GROUP BY
 
 $Customers -> group by $Customers_group = $.CustomerID into {$Customers_group, $Customers.age, total: count($) }; 
Name Age Orders
Joe 23 1
Mika 45 2
Lin 34 2
Sara 56 1
UPDATE

$Customer1= $Customers -> filter $.CustomerId == 1 -> transform {CustomerId: $.CustomerId, name: $.name ,age: 16} ; $Customerne1= $Customers -> filter $.CustomerId != 1 ; hdfs($Customer1 , $Customerne1);

Customer
CustomerID Name Age
1 Joe 26
2 Mika 45
3 Lin 34
4 Sara 56
5 Susan 18
INSERT
$Customer6=[{CustomerId:6,name: Terry ,age:50}]; hdfs($Customers , $Customer6); -> write(file( Customers.dat ));
Customer
CustomerID Name Age
1 Joe 23
2 Mika 45
3 Lin 34
4 Sara 56
5 Susan 18
6 Terry 50
DELETE
$Customerne1= $Customers ->filter $.CustomerId != 1
Customer
CustomerID Name Age
2 Mika 45
3 Lin 34
4 Sara 56
5 Susan 18

Leave a Reply

Your email address will not be published. Required fields are marked *