CODEWITHSUNDEEP
mongodbmongoosemongodb-query
Anuresh Verma
Anuresh Verma

Reputation: 1058

Or with If and In mongodb

{
  $cond: {
    if: { $in: [`$$${field.fieldName}`, ['', null]] },
    then: [],
    else: `$$${field.fieldName}`,
  },
},

In this condition, I also want to check the field.fieldName exits.

$exists: true,

then it will something like

if ( !filed.fieldName || field.fieldName === null || field.fieldName === '') then []

I am not finding any way to add $exits: true or false in this. Please help if can add something to this. Or any alternate way to achieve ?

Upvotes: 1

Views: 2423

Answers (4)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59557

Bear in mind, "field.fieldName exists" might be different to "field.fieldName is not null".

Consider special values like this:

db.collection.insertMany([
  { _id: 1, a: 1 }, 
  { _id: 2, a: '' }, 
  { _id: 3, a: undefined }, 
  { _id: 4, a: null }, 
  { _id: 5 }
])
db.collection.aggregate([
   {
      $set: {
         type: { $type: "$a" },
         ifNull: { $ifNull: ["$a", true] },
         defined: { $ne: ["$a", undefined] },
         existing: { $ne: [{ $type: "$a" }, "missing"] }
      }
   }   
])


{ _id: 1, a: 1,         type: "double",    ifNull: 1,    defined: true,  existing: true }
{ _id: 2, a: "",        type: "string",    ifNull: "",   defined: true,  existing: true }
{ _id: 3, a: undefined, type: "undefined", ifNull: true, defined: false, existing: true }
{ _id: 4, a: null,      type: "null",      ifNull: true, defined: true,  existing: true }
{ _id: 5,               type: "missing",   ifNull: true, defined: false, existing: false }

So, condition could be this one, depending on your requirements:

{
   $cond: {
      if: { $ne: [{ $type: "$field.fieldName" }, "missing"] },
      then: [],
      else: "$field.fieldName",
   }
}

For sake of completeness: With db.collection.find():

db.collection.find({ a: { $exists: false } })
  { _id: 5 }

db.collection.find({ a: { $exists: true} })
  { _id: 1, a: 1 }, 
  { _id: 2, a: '' }, 
  { _id: 3, a: undefined }, 
  { _id: 4, a: null }

db.collection.find({ a: null })
  { _id: 3, a: undefined }, 
  { _id: 4, a: null },
  { _id: 5 }

db.collection.find({ a: {$ne: null} })
  { _id: 1, a: 1 }, 
  { _id: 2, a: '' }, 

db.collection.find({ a: {$type: "null"} })
  { _id: 4, a: null }

Note, this behavior changed in MongoDB version 8.0: Queries for null Don't Match undefined Fields

Upvotes: 3

turivishal
turivishal

Reputation: 36144

You just need to put $ifNull in else part, if it's not exists it will return [],

{
  $cond: {
    if: {
      $in: [`$$${field.fieldName}`, ["", null]]
    },
    then: [],
    else: { $ifNull: [`$$${field.fieldName}`, []] }
  }
}

Playground

Input:

[
  { "key": null },
  { "key": "" },
  { "A": "a" }
]

Result:

[
  {
    "key": null,
    "status": []
  },
  {
    "key": "",
    "status": []
  },
  {
    "status": []
  }
]

Second approach, if you want to add an existing condition in if part you can try condition with $or,

  • $type will return the field's data type the missing field type is "missing"
{
  $cond: {
    if: {
      $or: [
        { $eq: [{ $type: `$$${field.fieldName}` }, "missing"] },
        { $in: [`$$${field.fieldName}`, ["", null]] }
      ]
    },
    then: [],
    else: `$$${field.fieldName}`
  }
}

Playground

Upvotes: 2

Tushar Gupta - curioustushar
Tushar Gupta - curioustushar

Reputation: 57105

You can use $or

$ifNull

Evaluates an expression and returns the value of the expression if the expression evaluates to a non-null value. If the expression evaluates to a null value, including instances of undefined values or missing fields, returns the value of the replacement expression.

{ $ifNull: [ <expression>, <replacement-expression-if-null> ] }

{
    $cond: {
      if: { 
        $or : [
            { $in: [`$$${field.fieldName}`, ['', null]] },
            { $ifNull: [`$$${field.fieldName}`, ""] }
        ]},
      then: [],
      else: `$$${field.fieldName}`,
    }
},

Upvotes: 3

Murat Colyaran
Murat Colyaran

Reputation: 2189

Try this:

{
  $cond: {
    if: { $ne : [`$$${field.fieldName}`, undefined] },
    then: [],
    else: `$$${field.fieldName}`,
  },
}

Upvotes: 1

Related Questions