MongoDB - Search by first attr with value

Question

Is it possible to do the same filtering as in JS?

const list = [
  {
    a: 1,
    "mostImportant": "qwer",
    "lessImportant": "rty"
  },
  {
    a: 2,
    "lessImportant": "weRt",
    "notImportant": "asd",
    
  },
  {
    a: 3,
    "mostImportant": "qwe2",
    "notImportant": "asd",
    
  }
];

list.filter((data) => {
  data.attrToSearch = data.mostImportant || data.lessImportant || data.notImportant;
  
  return data.attrToSearch.match(/wer/i);
});

in MongoDB?

Loot at this example: https://mongoplayground.net/p/VQdfoQ-HQV4

So I want attrToSearch to contain the value of the first, not blank attr with next order mostImportant, lessImportant, notImportant and then match by regex. The expected result is to receive the first two documents.

Appreciate your help.

Answer 1

Approach 1: With $ifNull

Updated

$ifNull only checks whether the value is null but does not cover checking for the empty string.

Hence, according to the attached JS function which skips for null, undefined, empty string value and takes the following value, you need to set the field value as null if it is found out with an empty string via $cond.

db.collection.aggregate([
  {
    $addFields: {
      mostImportant: {
        $cond: {
          if: {
            $eq: [
              "$mostImportant",
              ""
            ]
          },
          then: null,
          else: "$mostImportant"
        }
      },
      lessImportant: {
        $cond: {
          if: {
            $eq: [
              "$lessImportant",
              ""
            ]
          },
          then: null,
          else: "$lessImportant"
        }
      },
      notImportant: {
        $cond: {
          if: {
            $eq: [
              "$notImportant",
              ""
            ]
          },
          then: null,
          else: "$notImportant"
        }
      }
    }
  },
  {
    "$addFields": {
      "attrToSearch": {
        $ifNull: [
          "$mostImportant",
          "$lessImportant",
          "$notImportant"
        ]
      }
    }
  },
  {
    "$match": {
      attrToSearch: {
        $regex: "wer",
        $options: "i"
      }
    }
  }
])

Demo Approach 1 @ Mongo Playground

---

Approach 2: With $function

Via $function, it allows you to write a user-defined function (UDF) with JavaScript support.

db.collection.aggregate([
  {
    "$addFields": {
      "attrToSearch": {
        $function: {
          body: "function(mostImportant, lessImportant, notImportant) { return mostImportant || lessImportant || notImportant; }",
          args: [
            "$mostImportant",
            "$lessImportant",
            "$notImportant"
          ],
          lang: "js"
        }
      }
    }
  },
  {
    "$match": {
      attrToSearch: {
        $regex: "wer",
        $options: "i"
      }
    }
  }
])

Demo Approach 2 @ Mongo Playground