How to query AWS DocumentDB (DocDB) to compare two fields using $where

user_274030 0
Viewed 685

AWS's DocumentDB is not the same as Mongo -- specifically, you cannot use the $where operator in find queries. Specifically, I wish to make a query that will show me all results where document fields x == y, e.g.:

> db.my_collection.find({$where:"this.x==this.y"}).pretty();
Error: error: {
    "ok" : 0,
    "operationTime" : Timestamp(1623784861, 1),
    "code" : 303,
    "errmsg" : "Feature not supported: $where"
}

How is this done in DocDB?

aws-documentdb mongodb
1 Answers

The following is a possible workaround to get the results close to what you expect in DocumentDB. It is similar to a SQL self-join without the WHERE. You would need to extract the joined element if you want it exactly as it appears in MongoDB. Please note that the use of aggregate across large volumes may have performance implications when compared to $where operator. As always please do test before deploying to production.

Below are the steps used to test the query in DocumentDB

  • Sample data
db.tableA.insert( { "id":"123", "key1":"fjf", "key2":[{},{}] } )

db.tableA.insert( { "id":"123", "key1":"xyz", "key2":[{"x": "1"},{"y": "1"}], "key3": "123" } )

db.tableA.insert( { "id":"456", "key1":"abc", "key3": "456" } )
  • Query
db.tableA.aggregate([
{
    $lookup:
        {
          from: "tableA",
          localField: "id",
          foreignField: "key3",
          as: "joined"
        }
},
{ $unwind :"$joined" },
{  $redact: { $cond: [ { $eq: [ "$key3", "$joined.key3" ] }, "$$KEEP", "$$PRUNE" ] } }
{ $project: { joined: 1, _id: 0 } }
]).pretty()
user_49872230
Related