mongodb default value for document not in array

Question

I need to select id in array [1, 2, 3, 4, 5], if there is no document with Id 3 and 5, I want default name for this.

for example.

{id:1,name:a}
{id:2,name:b}
{id:4,name:c}

query something...

find ... id:{$in:{1,2,3,4,5}}})

-> result should be

{id:1,name:a}
{id:2,name:b}
{id:3,name:defaultvalue}
{id:4,name:c}
{id:5,name:defaultvalue}

i want default value for 3 and 5.

how should i query?? please help

Answer 1

You can achieve your use case using MongoDB aggregation framework

1. I started with $facet and created two arrays:
   • missingIDs to find out the missing IDs that are not in the documents
   • matchedDocuments to $project all the available documents available in array [1,2,3,4,5]
2. Then concatenated these two arrays, and used $addFields along with $ifNull to add a name field if null.

Try the following aggregation query:

db.collection.aggregate([
  {
    $facet: {
      missingIDs: [
        {
          $group: {
            _id: null,
            ids: {
              $push: {
                id: "$id"
              }
            }
          }
        },
        {
          $project: {
            _id: 0,
            ids: {
              $setDifference: [
                [
                  {
                    id: 1
                  },
                  {
                    id: 2
                  },
                  {
                    id: 3
                  },
                  {
                    id: 4
                  },
                  {
                    id: 5
                  }
                ],
                "$ids"
              ]
            }
          }
        },
        {
          $unwind: "$ids"
        },
        {
          $project: {
            id: "$ids.id"
          }
        }
      ],
      matchedDocuments: [
        {
          $match: {
            id: {
              $in: [
                1,
                2,
                3,
                4,
                5
              ]
            }
          }
        },
        {
          $project: {
            id: 1,
            name: 1,
            _id: 0
          }
        }
      ]
    }
  },
  {
    "$project": {
      concat: {
        "$concatArrays": [
          "$matchedDocuments",
          "$missingIDs"
        ]
      }
    }
  },
  {
    $unwind: "$concat"
  },
  {
    "$addFields": {
      "concat.name": {
        "$ifNull": [
          "$concat.name",
          "defaultvalue"
        ]
      }
    }
  },
  {
    $project: {
      id: "$concat.id",
      name: "$concat.name",
      
    }
  },
  {
    "$sort": {
      id: 1
    }
  }
])

MongoDB Playground

Answer 2

The pipeline contains the following stages in order -

1. Start by matching the id field
2. Group by nothing so that you can accumulate the result into an array for later set comparison
3. Find the set-difference of what is missing and attach them to the temporary result set in the $addFields stage
4. Project both matched and missing values now
5. Concat them both into a single array and project them
6. Unwind the array
7. Replace the root so that you get your desired flat structure
8. Sort by id field.

---

Here's the pipeline for your reference -

var idArr = [
    1,
    2,
    3,
    4,
    5
];

db.collection.aggregate([
    {
        $match: {
            id: {
                $in: idArr
            }
        }
    },
    {
        $group: {
            _id: null,
            grouping: {
                $push: '$$ROOT'
            }
        }
    },
    {
        $addFields: {
            missingVals: {
                $setDifference: [
                    idArr,
                    {
                        $map: {
                            input: '$grouping',
                            as: 'item',
                            in: '$$item.id'
                        }
                    }
                ]
            }
        }
    },
    {
        $project: {
            result1: {
                $map: {
                    input: '$grouping',
                    as: 'item',
                    in: {
                        id: '$$item.id',
                        name: '$$item.name'
                    }
                }
            },
            result2: {
                $map: {
                    input: '$missingVals',
                    as: 'item',
                    in: {
                        id: '$$item',
                        name: ''
                    }
                }
            }
        }
    },
    {
        $project: {
            result: {
                $concatArrays: ['$result1', '$result2']
            }
        }
    },
    {
        $unwind: '$result'
    },
    {
        $replaceRoot: {
            newRoot: '$result'
        }
    },
    {
        $sort: {
            id: 1
        }
    }
]);

Outputting -

[
  {
    "id": 1,
    "name": "a"
  },
  {
    "id": 2,
    "name": "b"
  },
  {
    "id": 3,
    "name": ""
  },
  {
    "id": 4,
    "name": "c"
  },
  {
    "id": 5,
    "name": ""
  }
]

MongoDb playground