MongoDB

The MongoDB driver is a specialized driver that can turn the documents with all specific arrays into columnar format making it possible to use with TARGIT InMemory Database and TARGIT Decision Suite.

It is optimized for speed and has been designed so developers without knowledge of MongoDB can write
SQL a simple SQL select statement to extract data from MongoDB.

Connection String Parameters:

The connection string parameter must be given as a standard MongoDB connection string, in this form.

mongodb://username:password@servername/databasename'

In case you’re connecting to a MongoDB version 2.x the authentication method must be set to legacy
MongoDB, this is done by changing the parameter to this form:

mongodb://username:password@servername/?authSource=nameofauthdb&authMechanism=MONGODB-CR

Query Parameters:

The MongoDB driver supports queries in the following formats:

SELECT <field1>,...,<fieldN> FROM <collection>.<array> WHERE <field1><operator><value> (AND|OR) <fieldN><operation><value>

1. To select the document identifier of current row the special keyword: root._id must be specified
   Example:

   SELECT root._id FROM values

This query selects identifiers for collection values.

2. To limit the amount of rows returned from a query the LIMIT clause can be utilized: LIMIT <value>
   Example:

   SELECT name FROM values LIMIT 10

This query returns the first 10 rows from collection values.

3. If you select data direct from collection use such syntax: FROM <collection>
   Example:

   SELECT name FROM values

This query selects name field from values collection.

4. If you want to select from array in collection use: FROM <collection>.<table>.<innerTable1>... <innerTableN>
   Example:

   SELECT array.name FROM values.array

This query selects names field in array object "array".

5. It is possible to select index for selected array using syntax <array_name>.INDEX() (only for MongoDB version 3.2 and higher).
   Example:

   SELECT array.name, array.INDEX() FROM values.array

6. You can select fields from object : <object_field>.<inner_field>
   Example:

   SELECT object.id, object.name FROM values

7. If you want to select all fields from array or object , then select data by fields of object or array.
   Example:

   SELECT my_object FROM values

   
   This query returns all fields of object my_object.

8. To filter the data the WHERE clause can be used.
   Example:

   SELECT name FROM values WHERE count > 10 AND order < 5 AND name="SEO" 

   Possible where operations: >, <, >=, <=, =, !=, IN, LIKE
9. Queries for dates must be made in the format of yyyy-MM-dd hh:mm:ss.
   Example:

   SELECT StartDate FROM date WHERE startDate > "2016-12-23 12:00:00"

Example:

LOAD ASSEMBLY TARGIT.MongoDB.dll' 
DATASOURCE mongodb=DOTNET CONNECTION 'MongoDB.MongoDBConnection' 
'mongodb://localhost/crmdata' 
IMPORT Contacts=mongodb.{SELECT 
Personal.FirstName,Personal.Surname,Personal.Title,Personal.JobTitle,Category,PersonID 
FROM Contacts}