Or with If and In mongodb

Question

{
  $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 ?

there is no need of exists condition because if filled does not exist then it will return null, so your null condition is enough. see working [playground](https://mongoplayground.net/p/Z7uZac6pezT), your condition is perfect. — turivishal, Jul 05 '21 at 11:41
@turivishal, but in case of non-existing field, he wants `[]` as result, not `null` — Wernfried Domscheit, Jul 05 '21 at 11:45
@WernfriedDomscheit, I think the else part removes the field when it does not exist, so he wants to prevent that. — turivishal, Jul 05 '21 at 12:01

score 3 · Answer 1 · answered Jul 05 '21 at 11:49

3

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}`,
    }
},

answered Jul 05 '21 at 11:49

Tushar Gupta - curioustushar

58,085
24
103
107

I don't think $ifNull condition needed, because if field does not exist it will return null, i think OP's query is perfect. you can check playground that i attached in above comment. – turivishal Jul 05 '21 at 11:53
1

@turivishal oh yes my bad, didn't notice that. Thank you. I think `$ifNull` will help OP . – Tushar Gupta - curioustushar Jul 05 '21 at 11:57
1

you are right, I think this is a good approach. – turivishal Jul 05 '21 at 12:10

Wernfried Domscheit · Answer 2 · 2023-04-24T13:29:49.597

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: "int",       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 }

Three dollars working fine. I am writing dynamic pipeline code. Just wanted to add $exits. — Anuresh Verma, Jul 05 '21 at 11:48

turivishal · Accepted Answer · 2021-07-05T12:09:48.650

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

score 1 · Answer 4 · answered Jul 05 '21 at 11:41

1

Try this:

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

answered Jul 05 '21 at 11:41

Murat Colyaran

2,075
2
8
27

Or with If and In mongodb

4 Answers4

Linked