Convert String to Int/double then compare doesn't always work with collection.find()

Working with Data
1

If my doc contains normal key-val pairs, the convert $toDouble/$toInt and then comparison works fine. But my doc looks like this:

{ "_id" : "14_0", "data" : [ { "dn" : "subdata=data_a", "userUL" : "0", "objectClass" : "NEWDATA", "userDL" : "5" } ] }

{ "_id" : "15_0", "data" : [ { "dn" : "subdata=data_b", "userUL" : "0", "objectClass" : "NEWDATA", "userDL" : "3" } ] }

{ "_id" : "16_0", "data" : [ { "dn" : "subdata=data_c", "userUL" : "0", "objectClass" : "NEWDATA", "userDL" : "9" } ] }

For an array it throws conversion error given below.

db.testcol.find({ $expr: { $lte: [ { $toDouble: "$data.userDL" }, 5 ] } }).count()

2020-09-18T06:26:37.010+0530 E  QUERY    [js] uncaught exception: Error: count failed: {
        "ok" : 0,
        "errmsg" : "Unsupported conversion from array to double in $convert with no onError value",
        "code" : 241,
        "codeName" : "ConversionFailure"
} :
_getErrorWithCode@src/mongo/shell/utils.js:25:13
DBQuery.prototype.count@src/mongo/shell/query.js:376:11
@(shell):1:1

So I tried with aggregate:

> db.testcol.aggregate([{ $project:{ adjustedGrades:{$map:{input: "$data.userDL",as: "grade",in: {$lte : [{ $toInt: "$grade" },5] } }}}}, {$match: {adjustedGrades: {$eq: true}}}])

{ "_id" : "14_0", "adjustedGrades" : [ true ] }
{ "_id" : "15_0", "adjustedGrades" : [ true ] }

I want to be able to convert my string to Int/Double and do the comparison inside find() and not aggregate(). I mean if the convert and compare is working for normal key-value pair using find() then, mongoDB should also be able to support for array data also right? Array data is a commonly given in docs and querying it should be as easy as — db.testcol.find({ $expr: { $lte: [ { $toDouble: “$data.userDL” }, 5 ] } }).count()

2

Hello : )

The error is because $data.userDL is an array.
When an array contains documents the path,selects that field from each document member,
and makes an array.

For the first document it is [5].

I don’t know why you what to use find only,and not aggregation stages,but if you want,
to use only find you can also.

This will be a filter to keep a document only if all userDL are >5.

{
  "$expr": {
    "$allElementsTrue": {
      "$map": {
        "input": "$data",
        "as": "d",
        "in": {
          "$gt": [
            {
              "$toDouble": "$$d.userDL"
            },
            5
          ]
        }
      }
    }
  }
}

With this


{ $expr: { $lte: [ { $toDouble: “$data.userDL” }, 5 ] } }

You mean check that all $data.userDL values are <5 (after type convertion).

Mongo json can be wrapped in functions so you can do that,without writing all the json.
Any time you want to check if all elements of a array satisfy a condition you can use this.

applyAllTrue(myarray,varname,myf)
{
  "$expr": {
    "$allElementsTrue": {
      "$map": {
        "input": "$"+myarray,
        "as": varname,
        "in": myf
      }
    }
  }
}

And call it like

applyAllTrue("data","member",{"$gt": [{"$toDouble": "$$member.userDL"},5]})

which is one line and simple.

Meaning run this function in all members of data array,and return true only if all
satisfy the function.

Instead of map and allElementsTrue i could use reduce,start from true and reduce to true
or false,map is used when i want to output an array,not a value like true/false,but map
works also,and looks simpler.

Json code can be generated,if something is common to use,making it a function helps

3

Hi Takis,
Thank you for your response. Yes, I want to use find() only because at the moment I have created some 200+ unique queries that use find(). Switching to aggregate would mean that I need to change these queries accordingly. So I am sticking to find() at the moment. The solution you provided worked fine at 1st glance, according to the docs I provided here (which is highly simplified). However when I add another object(containing different fields) to the “data” array the find(query) doesn’t work fine for the $gt… but works for $lte… which is weird. Can you help me here? Below I have added 1 more object for 1 of the docs:

{ "_id" : "14_0", "data" : [ { "dn" : "subdata=data_a", "userUL" : "0", "objectClass" : "NEWDATA", "userDL" : "5" } ] }
{ "_id" : "15_0", "data" : [ { "dn" : "subdata=data_b", "userUL" : "0", "objectClass" : "NEWDATA", "userDL" : "3" }, { "dn" : "subdata=data_z", "dummy" : "0", "objectClass" : "NEWDATAdummy", "user" : "something" } ] }
{ "_id" : "16_0", "data" : [ { "dn" : "subdata=data_c", "userUL" : "0", "objectClass" : "NEWDATA", "userDL" : "9" } ] }

When $gt is used:

> db.testcol.find({"$expr": {"$allElementsTrue": {"$map": {"input": "$data","as": "d","in": {"$gt": [{"$toInt": "$$d.userDL"},0]}}}}})
{ "_id" : "14_0", "data" : [ { "dn" : "subdata=data_a", "userUL" : "0", "objectClass" : "NEWDATA", "userDL" : "5" } ] }
{ "_id" : "16_0", "data" : [ { "dn" : "subdata=data_c", "userUL" : "0", "objectClass" : "NEWDATA", "userDL" : "9" } ] }

When $lte is used:

> db.testcol.find({"$expr": {"$allElementsTrue": {"$map": {"input": "$data","as": "d","in": {"$lte": [{"$toInt": "$$d.userDL"},10]}}}}})
{ "_id" : "14_0", "data" : [ { "dn" : "subdata=data_a", "userUL" : "0", "objectClass" : "NEWDATA", "userDL" : "5" } ] }
{ "_id" : "15_0", "data" : [ { "dn" : "subdata=data_b", "userUL" : "0", "objectClass" : "NEWDATA", "userDL" : "3" }, { "dn" : "subdata=data_z", "dummy" : "0", "objectClass" : "NEWDATAdummy", "user" : "something" } ] }
{ "_id" : "16_0", "data" : [ { "dn" : "subdata=data_c", "userUL" : "0", "objectClass" : "NEWDATA", "userDL" : "9" } ] }

[Update]
I think I need to use $allElementsTrue with $lte and $anyElementTrue with $gt.

4

Hello : )

The previous solution assumed that in $data array all emelemnts had userDL field.
But in the next data,one member doesn’t have userDL field and it doesnt work.

The below means

if for all documents in $data array
(have userDL field >0) or (dont have use usedDL field)
keep the document

Find() is fine,i only said it because when i started mongodb,i thought aggregation is
complicated,but now i think the opposite,that aggregation is so nice and powerful,and
you can do all with it, query/project/update.

$allElementsTrue => true is all true
$anyElementTrue => true if at least 1 true
Pick the one that does what you need.

{
  "$expr": {
    "$allElementsTrue": {
      "$map": {
        "input": "$data",
        "as": "d",
        "in": {
          "$cond": [
            {
              "$ne": [
                {
                  "$type": "$$d.userDL"
                },
                "missing"
              ]
            },
            {
              "$gt": [
                {
                  "$toInt": "$$d.userDL"
                },
                0
              ]
            },
            true
          ]
        }
      }
    }
  }
}
5

Thank you so much for your help and the solution provided :slight_smile:

1 Like
6

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.