Install jql

npm install jq-lang

JQL = JSON Query Language

JQL translates json to sql code. Syntax is ANSI sql, except offset and limit keywords (which is SQL Server). JQL is heavily influenced by mongo db.

JQL is designed to be integrated into General Endpoint (GE), a data driven http api.

Normalization Conventions

jql has 3 normalization conventions:

1. All tables must have an ID field that is primary key and autoincrement. This convention is mandatory.
2. FKs follow naming convention {tableName}ID.
3. FK lookup tables have a "name" field for display. Use a computed column for names that are composites.

JQL query object

Post body for query contains json object with these fields.

Property	Description	Type
fields	table column names. If not set, returns tableName.*	string[]
joins	Tables on which inner joins are performed. See join operations below	string[] or object[]
where	Object containing fields to form where clause. See Where clause operators below.	object
orderBy	array of strings of field names to order by. '-fieldName' for descending. 'tableName.fieldName' is valid.	string or string[]
children	table name for child records. if children property exists, it will return an additional array of objects for each child table in input array. pulls based on foreign key convention	string[]
offset	offset for starting select (requires order by and limit)	int
limit	limit the number of rows returned (requires order by and limit) typically used for paging.	int

Join Operations

Join operates by default on conventions. Default adds {joinTableName}Name to columns and joins based on naming convention. Use -tableName for left outer join.

jql	sql
joins: ["mytable"]	select mytableName... inner join [mytable] on [mytable].id = mytableID
joins: ["-mytable"]	select mytableName... left outer join [mytable] on [mytable].id = mytableID
joins: [{model:"mytable", fields:["myField1", "myField2"]}]	select myField1, myField2... inner join [mytable] on [mytable].id = mytableID
joins: [{model:"machine",on:{"[machine].ID":"[mytable].machineID"}}]	inner join [machine] on [machine].ID = [mytable].machineID

Where clause operators

JQL has mongo db style where clause operators.

property	effect
$ne	not equal <>
$gt	greater than >
$gte	greater than or equal to >=
$lt	less than <
$lte	less than or equal to <=
like	sql like operator

Examples

jql	sql
where: { active: 1, deleted: 0}	where active =1 and deleted = 0
where: { qty: { $gt: 20 } }	where qty > 20
where: { price: { $gt: 1, $lt: 2 }	where price > 1 and price < 2