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, your condition is perfect. — turivishal
– turivishal, Commented Jul 5, 2021 at 11:41
@turivishal, but in case of non-existing field, he wants [] as result, not null — Wernfried Domscheit
– Wernfried Domscheit, Commented Jul 5, 2021 at 11:45
@WernfriedDomscheit, I think the else part removes the field when it does not exist, so he wants to prevent that. — turivishal
– turivishal, Commented Jul 5, 2021 at 12:01

Wernfried Domscheit · Accepted Answer · 2024-08-05 06:24:57Z

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

Three dollars working fine. I am writing dynamic pipeline code. Just wanted to add $exits.

Tushar Gupta - curioustushar · Accepted Answer · 2021-07-05 11:49:46Z

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 5, 2021 at 11:49

Tushar Gupta - curioustushar

57.2k24 gold badges106 silver badges110 bronze badges

3 Comments

turivishal Over a year ago

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.

Tushar Gupta - curioustushar Over a year ago

@turivishal oh yes my bad, didn't notice that. Thank you. I think $ifNull will help OP .

turivishal Over a year ago

you are right, I think this is a good approach.

turivishal · Accepted Answer · 2021-07-05 12:09:48Z

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

Murat Colyaran · Accepted Answer · 2021-07-05 11:41:46Z

1

Try this:

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

answered Jul 5, 2021 at 11:41

Murat Colyaran

2,2072 gold badges13 silver badges34 bronze badges

Collectives™ on Stack Overflow

Or with If and In mongodb

4 Answers 4

1 Comment

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related