matching documents if a field is true, but if the same field is false check another field and match

Dylan_Campopiano1 · 2024-06-14T16:00:02.277Z

The problem is with the second object in the $and statement, If isDraft is false match the document otherwise check topicCreator

            {
                $match: {
                    $and: [
                        { topicTarget: new mongoose.Types.ObjectId(topicTarget), topicType: params?.topicType },
                        {
                            $or: [
                                { "isDraft": false },
                                { "topicCreator": new mongoose.Types.ObjectId(topicCreator) }
                            ]
                        },
                        {
                            $or: [
                                { isDeleted: { $exists: false }},
                                { $and: [
                                    {isDeleted: { $exists: true }},
                                    {isDeleted: false}
                                ]}
                            ]
                        }
                    ]
                }
            },```

I've tried making the second part of the $or into and $and to specify isDraft: true but that doesnt seem to do anything either

steevej · 2024-06-18T14:58:39.725Z

Please provide sample documents that matches and does not match the cases you want.

Also share everything else that you tried so we can avoid working in a direction you know does not work. It might also make us better understand the use case.

Craig_Crevola · 2024-06-20T07:47:27.388Z

Hi Dylan

A fallback condition can be achieved using a $switch statement. Replace the

Dylan_Campopiano1:

$or: [
                                { "isDraft": false },
                                { "topicCreator": new mongoose.Types.ObjectId(topicCreator) }
                            ]

condition with an expression of switch. I am not sure of the exact mongoose statement, however, you would do something like this:

$expr: {
        $switch: {
          branches: [
            { 
              case: {
                $eq:["$isDraft", false]
              }, then: true
            }],
          default: {
            $eq: ["$topicCreator", new mongoose.Types.ObjectId(topicCreator)]
          }
        }
      }

This is similar to SELECT CASE WHEN in SQL.

Hope that helps

Craig.