MongoDB Aggregation $in and return document in result even it does not exist-CodePudding

A query with an $in operator. If one of the values in the $in does not exist, I would still like to return a result for it.

For instance:

$match: {
      serviceIdRef: {
        $in: [
          "1",
          "2",
          "3"
        ]
      }

There are no serviceIdRef=3 in the collection. But I would still like to return a document in the result. For example:

{ 
    "_id=1",
    ..... // rest of data for _id=1
},
{ 
    "_id=2",
    ..... // rest of data for _id=2
},
{
    "_id": "3" // there is no entry in the collection but we still return this from the $in clause
}

Please see the Mongo Playground. Is that possible?

CodePudding user response：

There is no straight way to return not exists document in result, you have to do it after query in your client-side language, I am not sure what language you are using, but i have added in JS, and there are many logics in JS.

let queryIds = ["1", "2", "3"];
let result = [
  {
    "_id": "2",
    "serviceName": "my-service-2",
    "state": [
      {
        "count": 1,
        "pubState": "ACTIVE",
        "sample": [
          {
            "_id": "245",
            "pubName": "p1",
            "pubState": "ACTIVE",
            "serviceIdRef": "2",
            "serviceName": "my-service-2",
            "subName": "c1"
          }
        ]
      }
    ]
  },
  {
    "_id": "1",
    "serviceName": "my-service-1",
    "state": [
      {
        "count": 2,
        "pubState": "INVITED",
        "sample": [
          {
            "_id": "244",
            "pubName": "p1",
            "pubState": "INVITED",
            "serviceIdRef": "1",
            "serviceName": "my-service-1",
            "subName": "c1"
          },
          {
            "_id": "242",
            "pubName": "p1",
            "pubState": "INVITED",
            "serviceIdRef": "1",
            "serviceName": "my-service-1",
            "subName": "c1"
          }
        ]
      }
    ]
  }
];

const mergeArrays = (arr1 = [], arr2 = []) => {
   let res = [];
   res = arr1.map(_id => {
      const index = arr2.findIndex(el => el["_id"] == _id);
      const r = index !== -1 ? arr2[index] : {};
      return Object.assign({ _id }, r);
   });
   return res;
};

console.log(mergeArrays(queryIds, result));

CodePudding user response：

You can select something that doesn't exists by injecting documents in the pipeline.

On the first 3 stages of the pipeline you prepare the empty set, add and array of IDs you are after, and unwind them to create a template collection with 1 empty document per ID.

On the next stage you merge it with the actual collection using $lookup to itself. The key here is to use "preserveNullAndEmptyArrays" unwinding the matches, which will let you have empty documents.

The final 2 stages are just re-shaping documents to the original form.

db.collection.aggregate([
  {
    "$group": {
      "_id": null
    }
  },
  {
    $set: {
      "serviceIdRef": [
        "1",
        "2",
        "3"
      ]
    }
  },
  {
    $unwind: "$serviceIdRef"
  },
  {
    "$lookup": {
      "from": "collection",
      "localField": "serviceIdRef",
      "foreignField": "serviceIdRef",
      "as": "doc"
    }
  },
  {
    "$unwind": {
      path: "$doc",
      preserveNullAndEmptyArrays: true
    }
  },
  {
    "$project": {
      _id: 0,
      serviceIdRef: 1,
      doc: {
        "$mergeObjects": [
          {
            serviceIdRef: "$serviceIdRef",
            _id: null
          },
          "$doc"
        ]
      }
    }
  },
  {
    "$replaceRoot": {
      "newRoot": "$doc"
    }
  }
])

I didn't quite understand what you tried to do with in the playground pipeline, but I guess you can take it from here.

The documents without matching serviceIdRef will look like

{
  "_id": null,
  "serviceIdRef": "3"
}

So bear in mind the _id may not be unique as you may have more than 1 missing document.