MongoDB query to find records with keys containing dots?

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" } } }

AmitDiwan

Published on 02-Apr-2020 08:34:34

• Related Questions & Answers
• MySQL LIKE command doesn't work with strings containing dots to display records beginning with a specific number
• MongoDB query to find oldest date of three keys in each document
• MongoDB aggregation with multiple keys
• Find duplicate records in MongoDB?
• Return all ages records that are ints with a MongoDB query
• MongoDB Query to select records having a given key?
• MongoDB query to insert but limit the total records
• MongoDB query to get date records in a range
• MongoDB query to find and return subdocument with criteria?
• Find the records with % character in a LIKE query with MySQL
• MongoDB query to retrieve records from a collection named with letters and numbers
• MongoDB query to group records and display a specific value with dot notation
• Using find() to search for nested keys in MongoDB?
• Find MongoDB records with Price less than a specific value
• MongoDB query to find documents with specific FirstName and LastName