$switch inside a $match MONGODB-CodePudding

Hi i am trying to use MONGODB query inside TIBCO jasperstudio to create a report

What I am trying to do is filter the data using two parameters @orderitemuid and @ordercatuid. My case is if I put a parameter using @orderitemuid, it will disregard the parameter for @ordercatuid. Vise versa, if I put a parameter using @ordercatuid, it will disregard the parameter for @orderitemuid. But there is also an option when using bot parameters in the query. I used a $switch inside the $match but I am getting an error. Below is the $match I am using

{
    $match: {
        $switch: {
            branches: [
                {
                    case: { $eq: [{ $IfNull: [$P{orderitemuid}, 0] }, 0] },
                    then: { 'ordcat._id': { '$eq': { '$oid': $P{ordercatuid} } } },
                },
                {
                    case: { $eq: [{ $IfNull: [$P{ordercatuid}, 0] }, 0] },
                    then: { '_id': { '$eq': { '$oid': $P{orderitemuid} } } },
                },
            ],
            default: {
                $expr: {
                    $and: [
                        { $eq: ['_id', { '$oid': $P{orderitemuid} }] },
                        { $eq: ['ordcat_id', { '$oid': $P{ordercatuid} }] },
                    ],
                },
            },
        },
    },
}

Thank you in advance

CodePudding user response：

As mentioned in the $match docs

$match takes a document that specifies the query conditions. The query syntax is identical to the read operation query syntax; i.e. $match does not accept raw aggregation expressions. ...

And $switch is an aggregation expressions. this means it cannot be used in a $match stage without being wrapped with $expr.

You can however wrap it with $expr, this will also require you to restructure the return values a little bit, like so:

db.collection.aggregate([
  {
    $match: {
      $expr: {
        $switch: {
          branches: [
            {
              case: {
                $eq: [
                  {
                    $ifNull: [
                      $P{orderitemuid},
                      0
                    ]
                  },
                  0
                ]
              },
              then: {
                $eq: [
                  "$ordcat._id",
                  {"$oid":$P{ordercatuid}}
                ]
              }
            },
            {
              case: {
                $eq: [
                  {
                    "$ifNull": [
                      $P{ordercatuid},
                      0
                    ]
                  },
                  0
                ]
              },
              then: {
                $eq: [
                  "$_id",
                  {"$oid":$P{orderitemuid}}
                ]
              }
            }
          ],
          default: {
            $and: [
              {
                $eq: [
                  "$_id",
                  {"$oid": $P{orderitemuid} }
                ]
              },
              {
                $eq: [
                  "$ordcat_id",
                  {"$oid": $P{ordercatuid}}
                ]
              }
            ]
          }
        }
      }
    }
  }
])

Mongo Playground