MongoDB query to find records with keys containing dots?

MongoDBDatabaseBig Data Analytics

For this, use $addFields. In that, use the $objectToArray to get the data in the form of keys and values. Use $filter with $indexOfBytes to check if there are any keys with. inside it.

Let us first create a collection with documents −

> db.demo364.insertOne(
...   {
...       "details" : {
...          "details1.otherdetails.Name" : {"FirstName":"Chris" }
...       }
...    }
... );
{
   "acknowledged" : true,
   "insertedId" : ObjectId("5e57d485d0ada61456dc936d")
}
> db.demo364.insertOne(
...    {
...       "details" : {
...          "details1.otherdetails.Name" : {"FirstName":"David" }
...       }
...    }
... );
{
   "acknowledged" : true,
   "insertedId" : ObjectId("5e57d486d0ada61456dc936e")
}

Display all documents from a collection with the help of find() method −

> db.demo364.find();

This will produce the following output −

{ "_id" : ObjectId("5e57d485d0ada61456dc936d"), "details" : { "details1.otherdetails.Name" : { "FirstName" : "Chris" } } }
{ "_id" : ObjectId("5e57d486d0ada61456dc936e"), "details" : { "details1.otherdetails.Name" : { "FirstName" : "David" } } }

Following is the query to find records with keys containing dots −

> db.demo364.aggregate([
...    {
...       $addFields: {
...          result: {
...             $filter: {
...                input: { $objectToArray: "$details" },
...                cond: {
...                   $ne: [ { $indexOfBytes: [ "$$this.k", "." ] } , -1 ]
...                }
...             }
...          }
...       }
...    },
...    {
...       $match: {
...          $expr: {
...             $ne: [ { $size: "$result" }, 0 ]
...          }
...       }
...    },
...    {
...       $project: {
...          result: 0
...       }
...    }
... ])

This will produce the following output −

{ "_id" : ObjectId("5e57d485d0ada61456dc936d"), "details" : { "details1.otherdetails.Name" : { "FirstName" : "Chris" } } }
{ "_id" : ObjectId("5e57d486d0ada61456dc936e"), "details" : { "details1.otherdetails.Name" : { "FirstName" : "David" } } }
raja
Published on 02-Apr-2020 12:04:34
Advertisements