MongoDB search by first attr with value-CodePudding

Is it possible do 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 example: https://mongoplayground.net/p/VQdfoQ-HQV4

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

Appreciate your help

CodePudding user response：

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