Working with JSON and JQL

From Lianjapedia
Jump to: navigation, search

Overview

This document describes how you can work with JSON data in Lianja and introduces the JQL (pronounced Jackal) query language for JSON. JQL is the first database independent query language to leverage the complete flexibility of native JSON data types and the full power of SQL.

With JQL, JSON benefits from SQL because it enables developers to model and query data with relationships, and SQL benefits from JSON because it removes the "impedance mismatch" between the data model and the web/mobile application model.

The JQL query language is part of the Lianja database which is embedded into the Lianja Cloud Server.

Bm-noteicon.png
Pro Tip

You can use OData with disparate SQL databases using Virtual Tables

Features and Benefits of JQL

  • Create a data model that does not have traditional SQL query limitations
  • Build applications with ad-hoc, intelligent, and precise access to data
  • Incorporates enterprise class JSON member SQL query optimization
  • Leverage the entire SQL ecosystem for enterprise integration
  • SQL Database independence
  • OData API

Overview of JQL

JQL leverages the complete flexibility of JSON with the full power of SQL. Created specifically for use with Web and Mobile applications, JQL provides a common query language and JSON-based data model that operates independently from the underlying SQL database.

JQL is based on standard SQL. It has familiar data definition language (DDL), data manipulation language (DML), and query language statements.

The JQL DDL extends SQL with support for a native JSON data type.

The JQL DML provides transparent access to the (possibly nested) members of JSON data columns. You do not need to use any special functions or operators to reference JSON encoded object members as this is handled natively by the JQL engine.

Key features of JQL

JQL provides the traditional SQL CRUD operations using the INSERT, SELECT, UPDATE, and DELETE statements. These statements allow you to create, delete, and modify the data stored in SQL tables with native JSON data columns by specifying and executing simple commands. No special functions or operators are required to reference the members of a JSON data column.

The JQL CREATE TABLE Statement

create table json_test (id char(10), data json)
create table json_test2 (id char(10), data json)

The JQL INSERT Statement

insert into json_test values ('A100000000', "{'name':'Barry', 'region':'usa','total':2500}")
insert into json_test values ('A200000000', "{'name':'Chris', 'region':'uk','total':2500}")
insert into json_test values ('A300000000', "{'name':'Yvonne', 'region':'uk','total':2500}")
insert into json_test2 values ('A100000000', "{'name':'Barry', 'region':'usa','total':2500}")
insert into json_test2 values ('A200000000', "{'name':'Chris', 'region':'uk','total':2500}")
insert into json_test2 values ('A300000000', "{'name':'Yvonne', 'region':'uk','total':2500}")

The JQL SELECT Statement

JQL’s SELECT statement has the functionality of a standard SQL SELECT statement extended to work with native JSON data types. This lets you use your knowledge of SQL to work with JSON and provides simpler integration between web/mobile client JavaScript objects and the server side SQL database.

select id, data.name, data.region, data.total from json_test where data.name = 'Barry'

Indexes

You can use the CREATE and DROP INDEX statements to easily create indexes on JSON data members, and delete indexes respectively. The SQL query optimizer will utilize appropriate indexes as needed to speed up query (WHERE clauses) operations.

create index data_name on json_test (data.name)
create index data_name on json_test2 (data.name)
create index data_id on json_test (data.id)
create index data_id on json_test2 (data.id)

Sorting

JSON data members can be used in SELECT statements in the ORDER BY clause.

select id, rpad(data.name,20), rpad(data.region,10), data.total from json_test order by rpad(data.name,20)

Primary Key (PK) and Foreign Key (FK) Access

JSON data members can be used for primary key or foreign key access to data.

select json_test.data.name, json_test2.data.region from json_test inner join json_test2
       on json_test.data.id=json_test2.data.id

Aggregation

JSON data members can be used in standard SQL aggregation operations, such as MIN, MAX, COUNT as well as grouping operators, the GROUP BY clause, and the group filter HAVING.

select id, data.name, data.region, data.total from json_test group by data.name

Joins

JQL lets you retrieve data from multiple tables with the SQL join criteria specified containing JSON data member references. The SQL query optimizer will utilize appropriate indexes as needed to speed up the join operations.

select json_test.data.name, json_test2.data.region from json_test inner join json_test2
       on json_test.data.name=json_test2.data.name

The JQL UPDATE Statement

update json_test set data.name='Harry' where data.name='Barry'

The JQL DELETE Statement

delete from json_test where data.name='Barry'

The JQL REST OData API

JQL has been developed specifically to simplify the building of Web and Mobile applications and for that reason provides a REST OData API for performing CRUD operations from Web and Mobile applications.

The LianjaCloudDataServices.js client JavaScript library

The LianjaCloudDataServices.js JavaScript library provides a simple and small JavaScript client-side API for working with Lianja Cloud Server.

Note that the OData API is part of the LianjaWebFramework also so there is no need to include LianjaCloudDataServices.js.

The Lianja OData API

The Lianja OData API is simple and easy to use. The same API is available in both the complete LianjaWebFramework and the lightweight LianjaCloudDataServices.

There are several ways that you can use the OData API.

Using the OData API function calls

Create a new record

Lianja.OData_Create(url, data [, callback]);

Example:

var result = Lianja.OData_Create(
       "/odata/southwind/customers",
       { "customerid": "ABCD",
       ...
       }
);
if (!result)
{
       // Error: data cannot be created
}

Read one or more records

Lianja.OData_Read(url, callback, args);

Example:

Lianja.OData_Read(
    "/odata/yourdatabase/yourtable?$top=1&$skip=20&$rowid", // fetches row 21.
    function(status, result, args)
    {
        if (status) { // success
        }
        else { // failed
        }
    },
    args);

Update a record

Lianja.OData_Update(url, data, callback, args);

Example:

Lianja.OData_Update(
    "/odata/yourdatabase/yourtable?$rowid=10", // updates record 10
    { "name": "value", "amount":25.67, "date": "20140404", 
      "__olddata" : { "amount":20.00, "date": "20140404" } },
    function(status, result, args)
    {
        if (status) { // success
        }
        else { // failed
        }
    },
    args);

Delete a record

Lianja.OData_Delete(url, data, callback, args);

Example:

Lianja.OData_Delete(
    "/odata/yourdatabase/yourtable?$rowid=10", // deletes record 10
    { "name": "value", "amount":25.67, "date": "20140404" },
    function(status, result, args)
    {
        if (status) { // success
        }
        else { // failed
        }
    },
    args);

Using the OData API with jQuery

Create a new record

// declare a function that uses jQuery.
//
// url:              the odata URL
// data:             the data "object"
// callback:         a callback "function" called for async operation
// async:            true if async call false otherwise
// args              an args "object" that will be passed to your "callback" function
//
function create_data(url, data, callback, async, args)
{
       if (typeof callback !== 'function') async = false;
       else if (typeof async !== 'boolean') async = true;
 
       var request = $.ajax({
              url: url,
              contentType: "application/json",
              dataType: 'json',
              async: async,
              cache: false,
              type: "POST",
              data: data,
              mycallback: callback,
              myargs: args,
              success: function (response, textStatus, jqXHR) {
                     if (typeof this.mycallback === 'function')
                            this.mycallback(true, response, this.myargs);
              },
              error: function (response, textStatus, jqXHR) {
                     if (typeof this.mycallback === 'function')
                            this.mycallback(false, textStatus, this.myargs);
              }
       });
 
       if (!async)
       {
              return request.status == 200;
       }
       else
       {
              return false;
       }
};
var result = create_data(
       "/odata/southwind/customers",
       { "customerid": "ABCD",
       ...
       }
);

Read one or more records

function read_data(url, callback, async, args)
{
       if (typeof callback !== 'function') async = false;
       else if (typeof async !== ‘boolean’) async = true;
 
       $.ajax({
              url: url,
              type: "GET",
              dataType: 'json',
              contentType: "application/json",
              mycallback: callback,
              async: async,
              cache: false,
              myargs: args,
              success: function (response, textStatus, jqXHR) {
                     if (typeof this.mycallback === 'function')
                            this.mycallback(true, response, myargs);
              },
              error: function (response, textStatus, jqXHR) {
                     if (typeof this.mycallback === 'function')
                            this.mycallback(false, textStatus, myargs);
              }
       });
};

Update a record

function update_data(url, data, callback, async, args)
{
       if (typeof callback !== 'function') async = false;
       else if (typeof async !== ‘boolean’) async = true;
 
       var request = $.ajax({
              url: url,
              async: async,
              cache: false,
              type: "PUT",
              dataType: 'json',
              contentType: "application/json",
              data: data,
              mycallback: callback,
              myargs: args,
              success: function (response, textStatus, jqXHR) {
                     if (typeof this.mycallback === 'function')
                            this.mycallback(true, response, this.myargs);
              },
              error: function (response, textStatus, jqXHR) {
                     if (typeof this.mycallback === 'function')
                     this.mycallback(false, textStatus, this.myargs);
              }
       });
 
       if (!async)
       {
              return request.status == 200;
       }
       else
       {
              return false;
       }
};

Delete a record

function delete_data(url, data, callback, async, args)
{
       if (typeof callback !== 'function') async = false;
       else if (typeof async !== 'boolean') async = true;
       var request = $.ajax({
              url: url,
              async: async,
              cache: false,
              type: "DELETE",
              dataType: 'json',
              contentType: "application/json",
              data: data,
              mycallback: callback,
              myargs: args,
              success: function (response, textStatus, jqXHR) {
                     if (typeof this.mycallback === 'function')
                            this.mycallback(true, response, this.myargs);
              },
              error: function (response, textStatus, jqXHR) {
                     if (typeof this.mycallback === 'function')
                            this.mycallback(false, textStatus, this.myargs);
              }
       });
 
       if (!async)
       {
              return request.status == 200;
       }
       else
       {
              return false;
       }
};

Custom .rsp Web Service

You can also write a custom .rsp web service to retrieve JSON data using jQuery

The Lianja SQL engine has many extensions to standard SQL SELECT to provide a simpler and quicker way for generating JSON from a SQL SELECT.

Client-side jQuery call

function get_data(callback, async, args)
{
       if (typeof callback !== 'function') async = false;
       else if (typeof async !== ‘boolean’) async = true;
 
       var jqXHR = $.ajax({
              url: “get_data.rsp,
              type: "GET",
              dataType: 'json',
              contentType: "application/json",
              mycallback: callback,
              async: async,
              cache: false,
              myargs: args,
              success: function (response, textStatus, jqXHR) {
                     if (typeof this.mycallback === 'function')
                            this.mycallback(true, response, this.myargs);
              },
              error: function (response, textStatus, jqXHR) {
                     if (typeof this.mycallback === 'function')
                            this.mycallback(false, textStatus, this.myargs);
              }
       });
};
 
get_data(
       function(status, json_data)
       {
              if (status)
              {
                     // json_data contains an array of json objects
              }
              else
              {
                     // oops an error occurred
              }
       },
       true,
       {}
};

Server-side get_data.rsp web service to query data and send back as JSON

open database southwind
select * from orders into arrayofobjects console where customerid="wilmk"
// alternatively we don't need to open the database and can do this as a single command
select * from southwind!orders into arrayofobjects console limit 1,2