SQLScript is a JavaScript API that provides methods to the Array class in order
to query lists of objects for semantic information retrieval.
Roughly speaking, SQLScript provides methods for performing SQL-like queries
to lists of objects. By introducing methods like project, select, groupBy, sort, join_on
and limit all power of SQL can be applied to list of objects in order to retrieve semantic information.
SQLScript can be used to filter desired properties from an object list in
order to display the retrieved information in many ways. This can be done by using
the project method. The project method receives, as a formal parameter,
an object with the properties you want to retrieve from the object list, and their aliases.
For example:
var object_list= [{x:1, y:1, z:3},
{x:2, y:2, z:4},
{x:3, y:3, z:5}];
var fields= {
x:'with',
y:'height'
};
object_list._project(fields);
-> [{width:1, height:1},
{width:2, height:2},
{width:3, height:3}];
Let's see a more interesting example.
A project example
In this example I select a subset of a list of objects with properties: icon, tile, det, thumb, type, name, size, date
and details. Any subset of this properties can be retreived in a generic way by making a SQL-like query
and not considering any particular cases.
Array.prototype._groupBy
SQLScript can be used to group objects by using the groupBy method.
You only have to specify an array of keys that describe the required matching fields.
For example:
var object_list= [{x:1, y:1, z:3},
{x:2, y:2, z:3},
{x:3, y:3, z:5}];
var fields_to_match= ['z'];
object_list._groupBy(fields_to_match);
-> [{x:[1, 2], y:[1, 2], z:3},
{x:[3], y:[3], z:5}];
SQLScript can handle object arrays just like tables, and perform joining on specified fields.
This way complex sql queries can be done to arrays of objects.
The condition specified in
the join_on method can either be a comparison function or a string block. If you don't know what
string blocks are, read the SScript library page.
For example:
var obj_list= [{x:1, status:'married', z:3},
{x:2, status:'single', z:7}];
var obj_list2= [{id:1, name:'john', age:'18'}];
obj_list._join_on(obj_list2, ':a :b | ^a.x == b.id');
-> [{x:1, id:1, status:'married', z:3, name:'john', age:'18'}]
SQLScript can refine queries and handle order by using select and sort methods.
These methods are provided by the SScript library and can be used either with string blocks or functions.
For example:
var obj_list= [{x:1, status:'married', z:3},
{x:2, status:'single', z:7},
{x:3, status:'divorced', z:5}];
obj_list._sort(':a :b | ^a.status < b.status');
-> [{x:3, status:'divorced', z:5},
{x:1, status:'married', z:3},
{x:2, status:'single', z:7}];
obj_list._select(':a | ^a.x + a.z > 7.0');
-> [{x:3, status:'divorced', z:5},
{x:2, status:'single', z:7}];
You can append custom select and sort condition clauses to the Where and
Order objects. See the API reference page for details concerning the Order
and Where objects.
You can use the limit method to change the number of results returned by your query:
var obj_list= [{x:1, status:'married', z:3},
{x:2, status:'single', z:7},
{x:3, status:'divorced', z:5}];
obj_list._limit(2);
-> [{x:1, status:'married', z:3},
{x:2, status:'single', z:7}];
obj_list._limit(1, 1);
-> [{x:2, status:'single', z:7}]
The project method can also receive complex field structures. These structures describe
projected fields (as specified on top of page) and aggregate functions that can be used with the groupBy
method to accomplish advanced queries.
For example:
var object_list= [{x:1, y:1, z:3},
{x:2, y:2, z:3},
{x:3, y:3, z:5}];
var fields_to_match= ['z'];
var fields= {
x: {as:'with', aggregate:Aggregate["count"]},
y: {as:'height', aggregate:Aggregate["sum"]}
};
object_list._groupBy(fields_to_match)._project(fields);
-> [{width: 2, height: 3},
{width: 1, height: 3}];
For more information go to the API reference page
Another Example:
Conclusion
SQLScript is a powerful and handy extension of the SScript library, provinding all power of SQL queries to retrieve object information. This library has been tested and works with Opera 9+, IE 7 and Firefox 2+. Finally, I'd like to add a comparison table between common SQL queries and SQLScript queries:
SELECT fields
FROM table1
[JOIN table2 ON spec]
[WHERE condition]
[GROUP BY props
[HAVING condition2]]
[ORDER BY order]
[LIMIT limit]
table1
[._join_on(table2, spec)]
[._select(condition)]
[._groupBy(props)]
._project(fields)
[._select(condition2)]
[._sort(order)]
[._limit(limit)]
Picture this common scenario: a JSON array is retrieved as a response from a server and then rendered to an html table. If you wanted to refine your query, for each refinement you made, a new request to the server and the database would be made. Also, not all sql queries might be contemplated on the server side.
The first solution to this problem would be to program a user interface where dynamic queries can be done. However, this solution can be dangerous because people would have certain priviledges to server and database access. SQLScript provides a set of functions where all sql queries can be made without compromising the server or database.
