CODEWITHSUNDEEP
mongodbmongodb-query
Alex Zhukovskiy
Alex Zhukovskiy

Reputation: 10015

How to write a complex query using `expr` in mongodb?

I'm trying to write a bit complicated query to the MongoDB and I honestly don't know how to write it correctly. In SQL pseudocode it's something like:

SELECT *
FROM Something
WHERE 
   (IF Item.NestedItem THEN NULL ELSE Item.NestedItem.Id) == 'f4421f9e-5962-4c68-8049-a45600f36f4b'
OR (IF Item.NestedItem THEN NULL ELSE Item.NestedItem.OtherId) == 'd6b799dc-f464-4919-8435-a7b600cc408a'

I succeeded in writing IF as

{ "$cond" : [{ "$eq" : ["$Item.NestedItem", null] }, null, "$Item.NestedItem.Id"] }
{ "$cond" : [{ "$eq" : ["$Item.NestedItem", null] }, null, "$Item.NestedItem.OtherId"] }

But now I'm not sure how to compare them to constants and then join. I'm trying to write a translator from SQL-like language to MongoDb and this is example of query I cannot translate. I want something like

{ "$and" : 
    { "$eq" : { "$cond" : [{ "$eq" : ["$Item.NestedItem", null] }, null, "$Item.NestedItem.Id"] }, "f4421f9e-5962-4c68-8049-a45600f36f4b" },
    { "$eq" : { "$cond" : [{ "$eq" : ["$Item.NestedItem", null] }, null, "$Item.NestedItem.OtherId"] }, "d6b799dc-f464-4919-8435-a7b600cc408a" } }

But it's an invalid query as I get it

Upvotes: 1

Views: 186

Answers (1)

turivishal
turivishal

Reputation: 36114

The $cond is a aggregation expression, you need to use $expr before $and operation, in your SQL query you have user OR so just updating in query $and to $or

  • corrected $eq syntax after $or
db.collection.aggregate([
  {
    $match: {
      $expr: {
        $or: [
          {
            $eq: [
              {
                $cond: [
                  { $eq: ["$Item.NestedItem", null] }, 
                  null, 
                  "$Item.NestedItem.Id"
                ]
              },
              "f4421f9e-5962-4c68-8049-a45600f36f4b"
            ]
          },
          {
            $eq: [
              {
                $cond: [
                  { $eq: ["$Item.NestedItem", null] },
                  null,
                  "$Item.NestedItem.OtherId"
                ]
              },
              "d6b799dc-f464-4919-8435-a7b600cc408a"
            ]
          }
        ]
      }
    }
  }
])

Playground

Upvotes: 2

Related Questions