Inspire EMEA 2022 On-Demand is live! Watch now, and be sure to save the date for Inspire 2023 in Las Vegas next May.
We'll be completing standard maintenance for our Single Sign-On system on November 30th 6pm Pacific Time for approximately one hour. During this time My Alteryx and sign-in functionality to all My Alteryx programs (Licensing Portal, Community, Beta Program, Case Portal, and others) may be inaccessible. Thank you for your patience.

Alteryx Server Knowledge Base

Definitive answers from Server experts.

How To : Query MongoDB efficiently

JohnGD
Alteryx
Alteryx
Created

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 :

mongo_query.png


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
No ratings
Comments
LisaL
Alteryx
Alteryx

Don't forget $in

as shown about half-way down on this page.