Query on non empty object field having multiple keys or to check size of object greater than 1

Working with Data
,
1

We have one use case. Let’s suppose I have two documents as given below.

{
  "_id": ObjectID('123'),
  "test": {
     "a":1,
      "b":1
   }
},
{
  "_id": ObjectID('456'),
  "test": {
     "a":1
   }

Now I want those result whose “test” field has property other than “a” or in another way, I want those objects which have multiple keys/properties in a “test” field or to check the size of an object greater than 1

So, the result will be:

{
   "_id": ObjectID('123'),
   "test": {
      "a":1,
       "b":1
    }
}

I tried to make a query for the above output as shown below and it working as expected

db.col.find({"test": {"$gt": {"a": 1} }})

So, is that the right thing to do? Any downside of doing it? We want to lever-age indexes as well considering that we have indexes on ‘test’ field

Please let me know your inputs on this.

Thanks.

2

Hello @Aamod_Pisat, welcome to the MongoDB Community forum!

You can use the $exists query operator to check if a field exists or not - within a document. For example, the query

db.col.find( { ''test.a': { $exists: true } )

returns both the documents from your example collection.

db.col.find( { ''test.b': { $exists: true } )

returns only one document, the one with "_id": ObjectID('123').

3

Hi @Prasad_Saya, Thanks for your response. But I am not expecting that. $exists will return if the key is present or not. What I want is I would like to get that any other exists than given key in object field or I want is if that object has multiple keys or not i.e size of object length should be greater than 1.
So, for that, we used $gt operator. So, is that the right thing to do, Any downside of doing as we want to leverage indexes as well?

4

That is not the right query for it.

You have to write an aggregation query and use the $objectToArray aggregation operator to convert the object as an array and then find the length of the array. If the length of the array is greater than 1, then there are more than one field in the object. See the example from the documentation explains how to convert the object to an array.

1 Like
5

Okay thanks, @prasad. Using above, if I have index on ‘test’ field. So, will it leverage that or will this have any performance impact on querying documents

6

@Aamod_Pisat, I am not sure about that (I think it will not use the index). You can check if a query is using an index or not by using the explain on the query. The explain method generates a query plan which will have information about index usage by the query.

1 Like
7

HI @Prasad_Saya, We tried working with aggregation query using $objectToArray but the query doesn’t use indexes as it’s taking COLSCAN even though I have an index on that field.
But below query which I shared is taking index i.e. IXSCAN. So, hopefully, there won’t be any downside to doing this.

db.col.find({"test": {"$gt": {"a": 1} }})

Let us know your thoughts on this.

Thanks.

8

Hello @Aamod_Pisat, as I had mentioned earlier, you cannot use that query to count the number of fields in the test object (or sub-document ). The query you had posted doesn’t the number of fields - it only finds documents where the "test.a" value is greater than 1.

Yes, the index defined on the "test.a" will be used in the query - as you had mentioned there is an IXSCAN from the query plan.

Please note that the two queries have different purposes.

9

Hello @Aamod_Pisat, and welcome to the community,
you can use $jsonSchema as an alternative to @Prasad_Saya 's solution;

db.collection.find({$nor:[{
   "$jsonSchema":{
      "properties":{
         "test":{
            "type":"object",
	        "properties":{"a":{}},
	        "additionalProperties": false
         }
      }
   }
}]})
1 Like
10

Hi @Aamod_Pisat, Mongo stores json objects as bson, and so, the way to measure this value is using the key $bsonSize where the minimum size for an empty object is 5. Below is an example of…

{
    "$match": {
        "parameters_fields": {$gte: {$bsonSize: 5}},
//        "parameters_fields": {"$size": 1},
//        "parameters_fields": {$gte:  {"$size": 0}},
    }
}