Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Criteria syntax in MongoDB Input connector

msajones
5 - Atom

Help needed with MongoDB Input connector:)

I am attempting to limit the data set being pulled by using the Properties / Criteria input box.

When using NOSQLBooster, the query and criterial below run as expected but error in Alteryx.

 

Query in NOSQLBooster:  (Returns correct data)

db.getMongo().setReadPref("secondary");

var db   = db.getSiblingDB("UserData");

db.DeviceSettings.find({

        LastKnownSerialId: mb.regex.startsWith("A333335"),

        "Settings.Settings. ValueG ": true,

        "Color1": null

    },

    {

        _id: 0,

        "LastKnownId": 1,

        "Settings.Settings. ValueG ": 1

    })

    .sort({ _id: -1 })

    .limit(0) 

 

 

Error received when attempting to filter.

Error: MongoDB Input (4): Got parse error at "$", position 25: "SPECIAL_EXPECTED": could not parse JSON document

 

I need to return records from the sample JSON below with the following filter:

LastKnownID begins with “A333335”

and ValueG = “true”

and "Color1": null

 

 

Sample JSON

 

{

                "_id" : 2713599,

                "CorrelationId" : "4XXXXXXXXXXX1",

                "LastKnownId" : "A333335CCCCC",

                "SentByDeviceAt" : [

                                0,

                                0

                ],

                "ServerReceivedAt" : [

                                6777777777777,

                                0

                ],

                "Settings" : {

                                "Version" : 0,

                                "Settings" : {

                                                "ValueA" : true,

                                                " ValueB" : false,

                                                " ValueC" : 4,

                                                " ValueD" : 0,

                                                " ValueE" : true,

                                                " ValueF" : 0,

                                                " ValueG" : true,

                                                " ValueH" : false,

                                                " ValueI" : 8,

                                                " ValueJ" : 8,

                                                " ValueK" : 8,

                                                " ValueL" : 8,

                                                " ValueM" : 8

                                }

                },

                "color1" : null,

                "color2" : null

},

 

/* 2 */

{

----------------------- another document

},

 

/* 3 */

……..

3 REPLIES 3
KevinP
Alteryx Alumni (Retired)

 

@msajones The MongoDB Input tool when in automatic mode uses strict extended json for the criteria field. As such you have to specify the full exact json criteria for the find command to get the results you want without errors. For example your criteria for LastKnownId strings starting with 'A333335' would be:

 

 

{"LastKnownId" : { "$regex" : "^A333335.*", "$options" : ""}}

 

 

Adding other fields to this criteria is pretty simple as long as you keep this in mind. So if you wanted both the above string and documents where Color1 is NULL and/or if ValueG is true your criteria would be:

 

 

{"LastKnownId" : { "$regex" : "^A333335.*", "$options" : ""}, "Color1" : null}

or

{"LastKnownId" : { "$regex" : "^A333335.*", "$options" : ""}, "Settings.Settings.ValueG" : true}

or

{"LastKnownId" : { "$regex" : "^A333335.*", "$options" : ""}, "Settings.Settings.ValueG" : true, "Color1" : null}

 

**Note: for the ValueG examples I removed the leading white space from the field name assuming it was a typo. If the field does contain the white space this will need to be included in the above criteria. In this case replace 'Settings.Settings.ValueG' with 'Settings.Settings. ValueG'

 

 

 

For more information on strict extended JSON or the criteria options available for the find function please reference the following MongoDB documentation:

 

https://docs.mongodb.com/v3.4/reference/mongodb-extended-json/

https://docs.mongodb.com/v3.4/reference/method/db.collection.find/

 

msajones
5 - Atom

Thanks a lot Kevin! it works great! Just a little question about how to restrict to certain number of fields in the resulting outcome?

KevinP
Alteryx Alumni (Retired)

@msajones If you are referring to limiting the number of results provided by a MongoDB query you would add .limit(x) to your query. So for example a find statement with a limit of 100 results would look something like this:

 

db.CollectionName.find().limit(100)

Our MongoDB Input tool already encompasses this functionality with the Record Limit option. This option allows you to specify the integer value for the record limit and automatically applies the limit as per the above query example where 100 is replaced with the value specified.

 

If you are talking about limiting the fields returned this could in theory be done via the filter criteria. You would need to provide an appropriate expression to specify the fields you wanted. Considering the previous examples if we only want to return the same fields we are filtering on the criteria expression should look like this:

 

{"LastKnownId" : { "$regex" : "^A333335.*", "$options" : ""}, "Settings.Settings.ValueG" : true, "Color1" : null}, { "LastKnownId" : 1, "Settings.Settings.ValueG" : 1, "Color1" : 1 }

 

This example is based on MongoDB's documentation, but I wasn't able to get it to function as expected when I tested it. I am guessing since this is the only way select if a field is shown or not, that we are already appending this to the criteria in order to allow for the 'Include Mongo _id Field' option. If so this would explain why the query doesn't work as expected if you specify the fields manually. For more information you can reference:

 

https://docs.mongodb.com/manual/tutorial/project-fields-from-query-results/

 

Since this additional query criteria doesn't seem to function in the tool the best option to work around this is to use a Select tool after the MongoDB Input. The Select (or Dynamic Select) tool will allow you to choose which fields you want to keep or not keep as needed.

Labels