mongodb default value for document not in array

Viewed 1446

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

3 Answers

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

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

  • $match your ids $in condition
  • $group by null and make array of all root objects and ids
  • $reduce input as array of ids and initial value as root array of objects
    • check condition if id in ids array then return current value other wise add new default object and concat with root array using $concatArrays
  • $unwind deconstruct root array
  • $replaceWith to replace root object in root
let inputIds = [1,2,3,4,5];
let defaultValue = "defaultValue";

db.collection.aggregate([
  { $match: { id: { $in: inputIds } } },
  {
    $group: {
      _id: null,
      root: { $push: "$$ROOT" },
      ids: { $push: "$id" }
    }
  },
  {
    $project: {
      root: {
        $reduce: {
          input: inputIds,
          initialValue: "$root",
          in: {
            $cond: [
              { $in: ["$$this", "$ids"] },
              "$$value",
              { $concatArrays: ["$$value", [{ id: "$$this", name: defaultValue  }]] }
            ]
          }
        }
      }
    }
  },
  { $unwind: "$root" },
  { $replaceWith: "$root" }
])

Playground

Related