To perform a query and retrieve the content of several documents, you can use regular CreateAndFillPrepare or FillPrepare methods:

  R := TSQLORM.CreateAndFillPrepare(Client,WHERE_CLAUSE,[WHERE_PARAMETERS]);
  try
    n := 0;
    while R.FillOne do begin
      // here R instance contains all values of one document, excluding BLOBs
      inc(n);
    end;
    assert(n=COLL_COUNT);
  finally
    R.Free;
  end;

A WHERE clause can also be defined for CreateAndFillPrepare or FillPrepare methods.
This WHERE clause could contain several expressions, joined with AND / OR.
Each of those expressions could use:

  • The simple comparators = < <= <> > >=,
  • An IN (....) clause,
  • IS NULL / IS NOT NULL tests,
  • A LIKE operation,
  • Or even any ...DynArrayContains() specific function.

The mORMot ODM will convert this SQL-like statement into the optimized MongoDB query expression, using e.g. a regular expression for the LIKE operator.

The LIMIT, OFFSET and ORDER BY clauses will also be handled as expected.
A special care would be taken for an ORDER BY on textual values: by design, MongoDB will always sort text with case-sensitivity, which is not what we expect: so our ODM will sort such content on client side, after having been retrieved from the MongoDB server. For numerical fields, MongoDB sorting features would be processed on the server side.

The COUNT(*) function would also be converted into the proper MongoDB API call, so that such operations would be as costless as possible.
DISTINCT() MAX() MIN() AVG() functions and the GROUP BY clause would also be converted into optimized MongoDB aggregation pipelines, on the fly.
You could even set aliases for the columns (e.g. max(RowID) as first) and perform simple addition/substraction of an integer value.

Here are some typical WHERE clauses, and the corresponding MongoDB query document as generated by the ODM:

WHERE clause MongoDB Query
'Name=?',['Name 43'] {Name:"Name 43"}
'Age<?',[51] {Age:{$lt:51}}
'Age in (1,10,20)' {Age:{$in:[1,10,20]}}
'Age in (1,10,20) and ID=?',[10] {Age:{$in:[1,10,20]},_id:10}
'Age in (10,20) or ID=?',[30] {$or:[{Age:{$in:[10,20]}},{_id:30}]}
'Name like ?',['name 1%'] {Name:/^name 1/i}
'Name like ?',['name 1'] {Name:/^name 1$/i}
'Name like ?',['%ame 1%'] {Name:/ame 1/i}
'Data is null' {Data:null}
'Data is not null' {Data:{$ne:null}}
'Age<? limit 10',[51] {Age:{$lt:51}} + limit 10
'Age in (10,20) or ID=? order by ID desc',[30] {$query:{$or:[{Age:{$in:[10,20]}},{_id:30}]},$orderby:{_id:-1}}
'order by Name' {} + client side text sort by Name
'Age in (1,10,20) and IntegerDynArrayContains(Ints,?)',[10]) {Age:{$in:[1,10,20]},Ints:{$in:[10]}}
Distinct(Age),max(RowID) as first,count(Age) as count group by age {$group:{_id:"$Age",f1:{$max:"$_id"},f2:{$sum:1}}},{$project:{_id:0,"Age":"$_id","first":"$f1","count":"$f2"}}
min(RowID),max(RowID),Count(RowID) {$group:{_id:null,f0:{$min:"$_id"},f1:{$max:"$_id"},f2:{$sum:1}}},{$project:{_id:0,"min(RowID)":"$f0","max(RowID)":"$f1","Count(RowID)":"$f2"}}
min(RowID) as a,max(RowID)+1 as b,Count(RowID) as c {$group:{_id:null,f0:{$min:"$_id"},f1:{$max:"$_id"},f2:{$sum:1}}},{$project:{_id:0,"a":"$f0","b":{$add:["$f1",1]},"c":"$f2"}}

Note that parenthesis and mixed AND OR expressions are not handled yet.
You could always execute any complex query (e.g. aggregations or Map/Reduce) by using directly the TMongoCollection methods.

But for most cases, mORMot allows to share the same exact code between your regular SQL databases or NoSQL engines.
You do not need to learn the MongoDB query syntax: the ODM would compute the right expression for you, depending on the database engine it runs on.

Ensure you took a look at the updated documentation to better understand NoSQL/MongoDB integration with mORMot.
And feel free to use our forum for feedback, as usual!

Enjoy the NoSQL world!