Select


To query documents from Clusterpoint database SELECT statement can be used. It uses SELECT, WHERE, ORDER BY, GROUP BY and LIMIT clauses.

Basic SQL-like Syntax

Returning to an example of 'product' collection here is an example that returns first 100 documents ordered by price from 'product's collection:

SELECT name, price, availability
FROM product
WHERE price > 300
ORDER BY availability DESC
LIMIT 100 

JS/SQL Syntax

Clusterpoint uses JS/SQL as its query language, which means that you can actually use arbitrary JavaScript code in SELECT, WHERE and ORDER BY clauses, or to define functions before the SELECT clause.

Here is the general query structure showing where and how JavaScript can be used within the query:

[js_code]
SELECT select_column [, select_column ...]
FROM collection_name
[ (INNER | LEFT) JOIN collection_name [, collection_name ...] ON join_condition [, join_condition ...] ]
[ WHERE js_code ]
[ GROUP BY js_code [, js_code ...] ]
[ ORDER BY js_code [ASC | DESC] [, js_code [ASC | DESC] ...] ]
[ LIMIT [offset ,] row_count ]

select_column ::= js_code [AS alias]
collection_name ::= tag_name [AS tag_name]
alias ::= tag_name | "{valid JSON string}"
tag_name ::= {[a-zA-Z0-9\-_.]}
js_code ::= tag_name | {valid javascript that will get executed, last line will be return value}

Back to our example again -  this is a perfectly valid JS/SQL query:

SELECT name.replace("Bike", "Bicycle") 
FROM product
WHERE availability == "In Stock" || (availability == "Limited" && price > 500)
ORDER BY (price - order_price) / price


The simplest way to query a collection is just selecting documents in their raw format:

SELECT * FROM product
LIMIT 0,100

You can use LIMIT clause to specify how many documents you want to retrieve:

LIMIT offset, documents

* offset - defines how many documents you want to "skip";
* documents - defines how many documents you want to retrieve.

If LIMIT clause it is not specified, then database returns 10 documents. If ORDER BY parameter is not used, then documents are ordered by modification time - last modified (inserted, updated) - first shown.
If only one value is set - "LIMIT 100", it means that count of documents is specified (not more than 100 documents will be returned).

Note that in this case documents extactly as stored in JSON or XML format are returned, instead of result of evaluation of JavaScript expressions in context of the document as in the previous examples.

In the further sections you will find more information on how to use WHERE, ORDER BY and GROUP BY clauses to narrow, order and aggregate over result set.