SQL Select Analytics

Retrieves documents from the database and enables the selection of one or many documents or attributes from one or many collections.

Select executes 100% in database and utilizes database indexes when available. Select also utilizes sparse bit map indexes and can outperform commercial analytical SQL Engines in some cases.

The best way to learn how select handles documents and joins is to run queries in the query pad and analyze the output.


This feature is only available from your Nodechef Cloud Search instance. None of the examples provided below will work on a mongodb database. To deploy a cloud search instance, login and navigate to the dashboard. From deployments > deploy cloud search, you can then spin up a cloud search server
Cloud search supports the mongoDB extended JSON encoding in both strict and shell mode. This allows you to properly encode dates and binary types.

Syntax

SELECT [ ALL | DISTINCT ] { select_item [ [AS] output_name ] } [, ...] FROM [ from_collection | ( SUB SELECT.. ) ] WITH [ with_options ] [ INNER | LEFT JOIN condition ] [ WHERE condition ] [ GROUP BY expression [, ...] [ HAVING condition [, ...] ] ] [ ORDER BY { order_list [ ASC | DESC] } [, ...] ] [ LIMIT { count [ OFFSET start ] | start [ , count ] }


Mongo Shell example

db.runCommand({ select : "SELECT price, count(*) FROM books GROUP BY PRICE", params : { "?param1" : "test-parameter" } })


CURL Example

curl -X POST \ -H "X-Nodechef-Application-Id: ${APPLICATION_ID}" \ -H "Content-Type: application/json" \ -d '{"query": "SELECT price, count(*) FROM \ books group by price"" }' \ https://cs.api.nodechef.com/sql


Format of the SQL query object when using the REST API

{ "query" : "", "?param1" : <Specify the value of the parameter>, "?param2" : "" "?param3" : "" } Parameters have to be prefixed with the the question mark character. Parameters will be injected into the query at runtime.