Alteryx Designer Desktop Knowledge Base
Definitive answers from Designer Desktop experts.How to query a DateTime from Alteryx Server MongoDB using the MongoDB Input tool
Querying a date from the Alteryx Server Persistence MongoDB using the MongoDB Input tool can be tricky since some dates are stored as string values. Here is how to use regex in the criteria section of the MongoDB Input tool to query mongoDB documents on a datetime field.
The MongoDB Input tool in all instances of Alteryx Designer, Server can be used to retrieve data from the Alteryx Server MongoDB instance (embedded or User Managed)
Trying to query a date or use a function with a date within the MongoDB Input tool might yield no results when using the Criteria field in the MongoDB Input tool as in the screen shot below. This could happen because some datetime fields in the underlying MondoDB of Alteryx Server are actually string fields. Refer to the Help page for the MongoDB schemas to check the datatype of the field you want to query.
To query a DateTime value, that is stored as a string datatype, from the MongoDB through a MongoDB Input tool, regex will need to be used in the Criteria section of the tool. This will allow for all records on a specific day, month or year to be filtered. Example syntax of what might go in for the Criteria is as follows:
In the first screen shot the query is asking to get all records Greater Than ($gt) 2020-01-01, that can be done as follows:
To make this a bit more dynamic, the MongoDB Input tool could be put into a batch macro that passes in and updates a new regex pattern to match.
The MongoDB Input tool in all instances of Alteryx Designer, Server can be used to retrieve data from the Alteryx Server MongoDB instance (embedded or User Managed)
Trying to query a date or use a function with a date within the MongoDB Input tool might yield no results when using the Criteria field in the MongoDB Input tool as in the screen shot below. This could happen because some datetime fields in the underlying MondoDB of Alteryx Server are actually string fields. Refer to the Help page for the MongoDB schemas to check the datatype of the field you want to query.
To query a DateTime value, that is stored as a string datatype, from the MongoDB through a MongoDB Input tool, regex will need to be used in the Criteria section of the tool. This will allow for all records on a specific day, month or year to be filtered. Example syntax of what might go in for the Criteria is as follows:
{"CreationDateTime" : { "$regex": "2020-01-01.*", "$options": ""}}
This example would return all records with a CreationDateTime on 2020-01-01 because the .* in the regex will account for all iterations of time in the string.In the first screen shot the query is asking to get all records Greater Than ($gt) 2020-01-01, that can be done as follows:
{"CreationDateTime" : { "$regex": "2020.*", "$options": ""}}
This returns all records that have the year 2020 in it.To make this a bit more dynamic, the MongoDB Input tool could be put into a batch macro that passes in and updates a new regex pattern to match.
