jq-lang v0.0.15
Install jql
npm install jq-langJQL = 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 |