Alteryx Server Knowledge Base
Definitive answers from Server experts.How To : Query MongoDB efficiently
on - edited on by
Samantha_Jayne
How To: Query MongoDB efficiently
Looking for specific elements in MongoDB can sometimes be challenging.
This article provides a few commands to retrieve data (and look like a pro especially during a call with a customer).
Prerequisites
- Alteryx Server
- The server is up and running
- Access to the Alteryx System Settings to retrieve the Non-Admin password for MongoDB
- Minimal knowledge of the MongoDB structure
- Command Prompt/cmd.exe with Administrator rights
- Robo3T (Optional - for those who prefer a visual explorer for MongoDB)
Useful queries
Composition of a MongoDB query :
a. Retrieving an object in a collection, knowing its Id
Example : Find a workflow in the AlteryxGallery database (appInfos collection)
Version 1 :
db.getCollection('appInfos').find({"_id" : ObjectId("5cc30b2a18e70c230c2b5e9f")})
Version 2 :
db.getCollection('appInfos').find(ObjectId("5cc30b2a18e70c230c2b5e9f"))
b. Retrieving an object in a collection, using any other field
Example 1:Find users in the AlteryxGallery database by first name (users collection)
db.getCollection('users').find({"FirstName" : "Paul"})
Example 2:Find a user in the AlteryxGallery database by email address (users collection)
db.getCollection('users').find({"Email" : "bfrontz@alteryx.com"})
c. Perform a logical 'AND' to search using multiple criteria
Example:Find a user in the AlteryxGallery database based on their first name and last name (users collection)
db.getCollection('users').find({$and: [{"FirstName" : "Jane"}, {"LastName" : "Doe"}]})
d. Perform a logical 'OR' to search using multiple criteria
Example:For Windows Auth only - Make sure users information have been retrieved from Windows AD (windowsIdentityscollection)
db.getCollection('windowsIdentitys').find({$or: [{"Name" : "SRC\\sfraticelli"}, {"Name" : "SRC\\jdowe"}]})
* the '\' has to be added twice to be escaped
e. Retrieving the object added last in a collection
db.getCollection('versions').find().limit(1).sort({$natural:-1})
Example:Find the last added object in the AlteryxGallery database (versionscollection)
f. Query based on a relative date (objects created in the last X days, as an example)**
This queries the selected Collection relative to a date in milliseconds - you can see this is taking 30 days and converting it into milliseconds: 30 * 24 * 60 * 60 * 1000
You can enter any equation that results in milliseconds.
**This query can only be performed against "true" datetime/date type fields. Currently, the AlteryxGallery and AlteryxGallery_Lucene databases have datetime values. AlteryxService database stores all dates as String types, so this query will not work.
db.getCollection('sessions').find({ "CreationDate": { $gt: new Date(new Date() - 30 * 24 * 60 * 60 * 1000 ) } })
Example: Find the sessions created in the last 30 days.
BONUS - Text Matching — Contains, Starts / Ends With — Regular Expression
Example 1: Find a collection in the AlteryxGallery database whose name contains "test" (collections collection)
db.getCollection('collections').find({"Name" : /test/})
Example 2:Find a collection in the AlteryxGallery database whose name starts with "test" (collections collection)
db.getCollection('collections').find({"Name" : /^test/})
Example 3: Find a collection in the AlteryxGallery database whose name ends with "test" (collectionscollection)
db.getCollection('collections').find({"Name" : /test$/})
Additional Resources
Fun Fact
- Orcas (formerly known as killer whales), are actually dolphins - source
