npm install jq-lang
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.
jql has 3 normalization conventions:
- All tables must have an ID field that is primary key and autoincrement. This convention is mandatory.
- FKs follow naming convention {tableName}ID.
- FK lookup tables have a "name" field for display. Use a computed column for names that are composites.
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 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 |
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 |
| 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 |