Docs

SQL

Send a SQL string to the same server that stores the documents. One FROM, the database you connected to.

SQL runs inside the server. DataFusion plans it. The collections are the tables. A command-line client, a driver, and the ODBC driver all end up at this command:

db.runCommand({
  sql: "SELECT ename, job, sal FROM emp WHERE sal > 2000"
})

The database is $db. In the shell, that is the database you opened. There is one FROM. A join is outside this statement. Combine collections with $lookup in the pipeline, then read the result, or write it with $out and select from that collection.

What a statement can do

SELECT with a target list, including SELECT 1 AS n with no FROM.
WHERE with comparisons. One comparison, such as sal > 1000 or job = 'CLERK', uses the same index a find would use. DataFusion checks the predicate again.
COUNT(*) and GROUP BY.
SHOW INDEXES FROM emp, also SHOW INDEX and SHOW KEYS.

Names are case-sensitive, and they are the collection names. The sample uses lowercase emp so FROM emp hits those rows. FROM EMP is a different collection.

db.runCommand({
  sql: "SELECT job, COUNT(*) AS n, SUM(sal) AS payroll FROM emp GROUP BY job"
})

The cursor rows use the column names from the statement. Integers you stored as BSON ints come back as integers.

Text in the WHERE clause

WHERE TEXT '…' is handled before the SQL planner, because a bare TEXT token is not standard SQL. It is the same search as $text. The view or collection needs the text index. After the sample is loaded, that view is emp_search.

SELECT ename, job, sal FROM emp_search WHERE TEXT 'clerk'
SELECT ename, job, sal FROM emp_search WHERE TEXT 'clrek' FUZZY 0.3
SELECT facet, bucket, doc_count
FROM emp_search
WHERE TEXT 'clerk'
FACETS job TERMS, sal RANGES (1500), (1500, 3000), (3000)

FUZZY takes a fraction from 0 to 1. FACETS replaces the selected columns with three: facet, bucket, doc_count. TERMS buckets by value. RANGES buckets a number. The open end (1500) is everything up to 1500, and a trailing (3000) is everything above 3000. The full rules are on Text search.

From a client that only speaks SQL

The ODBC driver sends the string you typed and returns the rows. Connection keys are on the ODBC page. The server must already be running. SQL does not start it.