Query Object

ResQL's query builder generates SQL from an intermediate JSON format, which can express the most essential subset of SELECT, INSERT, UPDATE and DELETE syntax. This is documentation of that format.

The specs below are in an informal JSON-flavored EBNF-like notation where [ ... ] and { ... } have their meanings from JSON, ( ... ), | and ? have grammar notation semantics (grouping, alternatives, optional).

Query		:= SelectQuery | InsertQuery | UpdateQuery | DeleteQuery

SelectQuery	:= {
	verb:    "select",
	table:   table_name,
	joins:   [ table_name, ... ], ?
	columns: [ (column_name | Aggregation), ... ], ?
	filters: { column_name: Condition, ... }, ?
	order:   [ Column, ("asc" | "desc") ], ?
	limits:  [ lower, count ] ?
}

InsertQuery	:= {
	verb: "insert",
	table: table_name,
	data: [{ column_name: Value, ... } ...] | SelectQuery
}

UpdateQuery	:= {
	verb: "update",
	table: table_name,
	data: { column_name: Value, ... } | SelectQuery,
	filters: { column_name: Condition, ... }, ?
	limit: count ?
}

DeleteQuery	:= {
	verb: "delete",
	table: table_name,
	filters: { column_name: Condition, ... }, ?
	limit: count ?
}

Aggregation	:= [ aggregate_function, column_name ]
Condition	:= Value | [ sql_operator, Value?] ]
Value		:= sql_value | SelectQuery

Notes

Joins are always left outer joins.

Default limits

• For select queries, [0, 20]
• For updates and deletes, 1

Aggregations

• [ ["count", "id"] ] will become SELECT COUNT(id) as count_id with no GROUP BY
• [ ["sum", "amount"], "customerId" ] will become SELECT customerId, SUM(amount) as sum_amount ... GROUP_BY customerId
• You can use the generated names sum_amount etc. as filter keys.
• Filters will be in WHERE if the relevant column is in the columns list, otherwise in HAVING.

Examples

SELECT * FROM students WHERE joined<'2012' ORDER BY name LIMIT 0, 10 

can be written as

{
	"verb": "select",
	"columns": "*",
	"table": "students",
	"filters": {"joined": ["<", "2012"]}
}