Criteria syntax in MongoDB Input connector

msajones

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 */

……..

KevinP

@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/