Simple JSON Activate Qualification

JSON can be used to create activate qualification. Following samples are based on the Order LOD from the Northwind application.

Simple qualification by attribute

{
    "OrderId": 10
}

Generates the following SQL:

SELECT ORDERID, ORDERDATE, SHIPPEDDATE, REQUIREDDATE, FREIGHT, SHIPNAME, SHIPADDRESS, SHIPCITY, SHIPREGION, SHIPPOSTALCODE,
       SHIPCOUNTRY, EMPLOYEEID, CUSTOMERID, SHIPPERID
FROM  orders
WHERE orders.ORDERID = 10;

Simple qualification by multiple attribute values.

Values are 'or'd together.

{
    "OrderId": [ 10, 11, 12 ]
}

Generates the following SQL:

SELECT ORDERID, ORDERDATE, SHIPPEDDATE, REQUIREDDATE, FREIGHT, SHIPNAME, SHIPADDRESS, SHIPCITY, SHIPREGION, SHIPPOSTALCODE,
       SHIPCOUNTRY, EMPLOYEEID, CUSTOMERID, SHIPPERID
FROM  orders
WHERE  ( orders.ORDERID = 10 OR orders.ORDERID = 11 OR orders.ORDERID = 12 ) ;

Qualification using multiple attributes

Multiple attribute qualifications will be 'and'd together.

{
    "OrderDate": "2016-12-01",
    "ShippedDate": "2016-12-01"
}

Generates the following SQL:

SELECT ORDERID, ORDERDATE, SHIPPEDDATE, REQUIREDDATE, FREIGHT, SHIPNAME, SHIPADDRESS, SHIPCITY, SHIPREGION, SHIPPOSTALCODE,
       SHIPCOUNTRY, EMPLOYEEID, CUSTOMERID, SHIPPERID
FROM  orders
WHERE orders.ORDERDATE = '2016-12-01' AND orders.SHIPPEDDATE = '2016-12-01';

Qualification using multiple attributes with OR

Multiple attribute qualifications can be 'or'd using the 'or' field name.

{
    "or": [ 
        { "OrderDate": "2016-12-01" },
        { "ShippedDate": "2016-12-01" }
    ]
}

Generates the following SQL:

SELECT ORDERID, ORDERDATE, SHIPPEDDATE, REQUIREDDATE, FREIGHT, SHIPNAME, SHIPADDRESS, SHIPCITY, SHIPREGION, SHIPPOSTALCODE,
       SHIPCOUNTRY, EMPLOYEEID, CUSTOMERID, SHIPPERID
FROM  orders
WHERE  ( orders.ORDERDATE = '2016-12-01' OR orders.SHIPPEDDATE = '2016-12-01' ) ;

Using operators other than '='.

Valid operators are:

• <, lt
• <=, lte
• >, gt
• >=, gte
• =, ==, eq
• !=, <>, neq
• like
• ilike

To explicitly specify an operator, use:

{
    "OrderDate": { ">": "2016-12-01" }, 
    "ShippedDate": { "lt": "2016-12-01" }
}

Generates the following SQL:

SELECT ORDERID, ORDERDATE, SHIPPEDDATE, REQUIREDDATE, FREIGHT, SHIPNAME, SHIPADDRESS, SHIPCITY, SHIPREGION, SHIPPOSTALCODE,
       SHIPCOUNTRY, EMPLOYEEID, CUSTOMERID, SHIPPERID
FROM  orders
WHERE orders.ORDERDATE > '2016-12-01' AND orders.SHIPPEDDATE < '2016-12-01';

Qualifying the root using a child entity

To qualify the root with a value from child entity, use the entity name.

{
    "OrderId": { "<": 10 }, 
    "Customer": { 
       "CustomerId": 10
    }
}

Generates the following SQL:

SELECT ORDERID, ORDERDATE, SHIPPEDDATE, REQUIREDDATE, FREIGHT, SHIPNAME, SHIPADDRESS, SHIPCITY, SHIPREGION, SHIPPOSTALCODE,
       SHIPCOUNTRY, EMPLOYEEID, CUSTOMERID, SHIPPERID
FROM  orders
WHERE orders.ORDERID < 10 AND orders.CUSTOMERID = '10';

Another example:

{
    "OrderId": { "<": 10 }, 
    "Customer": { 
       "CompanyName": "Acme"
    }
}

Generates the following SQL:

SELECT orders.ORDERID, orders.ORDERDATE, orders.SHIPPEDDATE, orders.REQUIREDDATE, orders.FREIGHT, orders.SHIPNAME,
       orders.SHIPADDRESS, orders.SHIPCITY, orders.SHIPREGION, orders.SHIPPOSTALCODE, orders.SHIPCOUNTRY,
       orders.EMPLOYEEID, orders.CUSTOMERID, orders.SHIPPERID
FROM  orders LEFT JOIN
       customers ON customers.CUSTOMERID = orders.CUSTOMERID
WHERE orders.ORDERID < 10 AND customers.COMPANYNAME = 'Acme';

Using "Restrict"

To qualify a child entity when it is loaded, use 'restrict' at the top level:

{
    "OrderId": 10,
    "restrict": {
        "OrderDetail": {
            "Product" : { "ProductId": 11 }
        }
    }
}

Generates the following SQL:

SELECT ORDERID, ORDERDATE, SHIPPEDDATE, REQUIREDDATE, FREIGHT, SHIPNAME, SHIPADDRESS, SHIPCITY, SHIPREGION, SHIPPOSTALCODE,
       SHIPCOUNTRY, EMPLOYEEID, CUSTOMERID, SHIPPERID
FROM  orders
WHERE orders.ORDERID = 10248;

SELECT UNITPRICE, QUANTITY, DISCOUNT, PRODUCTID, ORDERID
FROM  orderdetails
WHERE (orderdetails.ORDERID = 10248 AND ( orderdetails.PRODUCTID = 11));

rootOnly keyword

To activate only the root entities, use the rootOnly keyword:

{
    "OrderDate": { ">": "2016-12-01" }, 
    "rootOnly": true
}

orderBy keyword

Follow are all examples of orderBy:

      {
        orderBy: "age"
        orderBy: { age: "desc" }
        orderBy: [ { age: "desc" }, { gender: "asc"} ]
      }

Code samples

Scala:

val order = task.Order.activate{ _.fromJson( """{ "OrderId": 10 } """) }

Ruby:

order = task.Order.activate( OrderId: 10, rootonly: true )

Javascript/Typescript:

let order = Order.activate( { OrderId: 10, rootonly: true } );

VML:

// Not currently available in VML

Java:

QualificationBuilder qb = new QualificationBuilder( task );
qb.setLodDef( "Order" );
qb.loadFromJsonString( "{ \"OrderId\": 10 } " );
qb.activate();

Qualification keyword aliases.

The following keywords are aliases of each other and you may use whatever form is most readable for you. Note that some keywords may cause problems if you have entities or attributes with the same name (e.g. "restrict"). In those situations use the keyword alias that is not a valid entity/attribute name (e.g. "$restrict").

• $restrict, restrict, $restricting, restricting.
• $or, or
• $and, and
• <, $lt, lt
• <=, $lte, lte
• >, $gt, gt
• >=, $gte, gte
• !=, <>, $neq, neq
• $rootOnly, rootOnly
• $like, like
• $ilike, ilike