sql Command

There is a sql command which will accept any sort of data as JSON, marshal it into a query object and allow you to alias, filter, order, and limit the rows on the fly using CFML's query of queries.

Parameters

• data - The JSON to process

• select - A SQL list of column names, eg. "name,version"

• where - A SQL where filter used in a query of queries, eg. "name like '%My%'"

• orderby - A SQL order by used in a query of queries, eg. "name asc, version desc"

• limit - A SQL limit/offset used in a query of queries, eg. "5" or "5,10" (eg. offset 5 limit 10)

• headerNames - An list of column headers to use (used for array of arrays)

When using an array of arrays and not specifying headerNames, the columns will be named col_1, col_2, col_3, etc...

Examples

Filter, sort, limit, and select extensions installed into the CLI (output as table)

#extensionlist  | sql select=id,name where="name like '%sql%'" orderby=name limit=3 | printTable

Order and select JSON data from a file (output as JSON)

cat myfile.json | sql select=col1,col2 orderby=col2

Limit JSON (output as table)

sql data=[{a:1,b:2},{a:3,b:4},{a:5,b:6}] where="a > 1" | printTable

The sql command works very nicely with the tablePrinter command.