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
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
You can use LIMIT clause to specify how many documents you want to retrieve:
LIMIT offset, documents
* offset - defines how many documents you want
* 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).
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.