How to use $cond in mongoDB

Question

Please refer the attached image and help me explain this case. Why didn't cova_items column assigned value of InStock column when woo_order_id is null? query command:

db.cova_order.aggregate([
    {
        $project:{
            InStock:1,
            woo_order_id:1,
            woo_order_data:1,
            cova_items:{
                    $cond: { if: { $eq:["$woo_order_id",null] }, then:"$InStock" , else:"$woo_order_data" }
            }
        }
    },

=> Result: cova_items column shows value like as woo_order_data column

Please show me the mistake if having. Thanks

Seems `woo_order_id` is not null, but empty. So try: `{ $eq:["$woo_order_id",""] }` — Valijon, Aug 11 '23 at 18:19
Please don't paste screenshots, use formatted text. See https://meta.stackoverflow.com/q/285551 — Wernfried Domscheit, Aug 12 '23 at 07:28
It depends what you are looking for. Do you really like to check for "is null" or something else. See https://stackoverflow.com/questions/68255363/or-with-if-and-in-mongodb/68255564#68255564 — Wernfried Domscheit, Aug 12 '23 at 07:32
Just for the sake of completeness, you should **never** store date values as (even localized) strings, it's a design flaw. Store always proper `Date` objects. — Wernfried Domscheit, Aug 12 '23 at 07:36

score 1 · Answer 1 · answered Aug 11 '23 at 18:54

$cond has simplified syntax.

{ $cond: [ <boolean-expression>, <true-case>, <false-case> ] }

Fast solution:

Seems your $woo_order_id is not null, but empty.

{$cond:[ {$eq:["$woo_order_id", ""] }, "$InStock", "$woo_order_data"]}

Complete solutuion

Whether $woo_order_id can be null, undefined or missing, with the $ifNull operator we cover them all.

db.cova_order.aggregate([
  {
    $project: {
      InStock: 1,
      woo_order_id: 1,
      woo_order_data: 1,
      cova_items: {
        $cond: [
          { $eq: [ { "$ifNull": [ "$woo_order_id", ""] }, "" ] },
          "$InStock",
          "$woo_order_data"
        ]
      }
    }
  }
])

MongoPlayground

Hi Valijon, Thanks so much. The issue has already been resolved — MenThan, Aug 14 '23 at 04:29

Jerry · Accepted Answer · 2023-08-11T19:29:56.577

Given Data:

Task : If woo_order_id is null or "" (empty string) then return Instock and if it is not null/empty string then return woo_order_data

[
  {
    "InStock": 1,
    "woo_order_id": null,
    "woo_order_data": "1d",
    
  },
  {
    "InStock": 2,
    "woo_order_id": 2,
    "woo_order_data": "2d",
    
  },
  {
    "InStock": 3,
    "woo_order_id": 3,
    "woo_order_data": "3d",
    
  },
  {
    "InStock": 4,
    "woo_order_id": "",
    "woo_order_data": "4d",
    
  }
]

Solution:

db.collection.aggregate([
  {
    $project: {
      InStock: 1,
      woo_order_id: 1,
      woo_order_data: 1,
      cova_items: {
        $cond: {
          if: {
            "$or": [
              {
                "$eq": [
                  "$woo_order_id",
                  ""
                ]
              },
              {
                "$eq": [
                  "$woo_order_id",
                  null
                ]
              }
            ]
          },
          then: "$InStock",
          else: "$woo_order_data"
        }
      }
    }
  }
])

Output :

[
  {
    "InStock": 1,
    "_id": ObjectId("5a934e000102030405000000"),
    "cova_items": 1,
    "woo_order_data": "1d",
    "woo_order_id": null
  },
  {
    "InStock": 2,
    "_id": ObjectId("5a934e000102030405000001"),
    "cova_items": "2d",
    "woo_order_data": "2d",
    "woo_order_id": 2
  },
  {
    "InStock": 3,
    "_id": ObjectId("5a934e000102030405000002"),
    "cova_items": "3d",
    "woo_order_data": "3d",
    "woo_order_id": 3
  },
  {
    "InStock": 4,
    "_id": ObjectId("5a934e000102030405000003"),
    "cova_items": 4,
    "woo_order_data": "4d",
    "woo_order_id": ""
  }
]