How do I check if value exists in both arrays in MongoDB?

January 17, 2023

In MongoDB I have the following document:

directConfigs: [
    {
        id: ObjectId('627279d3ba7aef5d6418c867')
        name: "Config A"
    },
    {
        id: ObjectId('628e32777b9e83619746ee3f')
        name: "Config B"
    }
],
indirectConfigs: [
    {
        id: ObjectId('627279d3ba7aef5d6418c867')
        name: "Config A"
    },
    {
        id: ObjectId('628b4d3ff5b1c1736c0b654a')
        name: "Config C"
    }
]

I want to make a project that says if the config exist in both directConfigs and indirectConfigs then add a field to it called type and set the value to "Both", and if it only exists in one of the fields, add a value called direct or indirect depending on what field it exist in. And this should return an array looking like this:

configs: [
    {
        id: ObjectId('627279d3ba7aef5d6418c867')
        name: "Config A"
        type: "Both"
    },
    {
        id: ObjectId('628e32777b9e83619746ee3f')
        name: "Config B"
        type: "direct"
    },
    {
        id: ObjectId('628b4d3ff5b1c1736c0b654a')
        name: "Config C",
        type: "indirect"
    }
]

>Solution :

One option is to use $setIntersection and $setDifference:

db.collection.aggregate([
  {$set: {both: {$setIntersection: ["$directConfigs", "$indirectConfigs"]}}},
  {$set: {
      direct: {$setDifference: ["$directConfigs", "$both"]},
      indirect: {$setDifference: ["$indirectConfigs", "$both"]}
  }},
  {$project: {
    configs: {$concatArrays: [
        {$map: {input: "$both", in: {$mergeObjects: ["$$this", {type: "both"}]}}},
        {$map: {input: "$direct", in: {$mergeObjects: ["$$this", {type: "direct"}]}}},
        {$map: {input: "$indirect", in: {$mergeObjects: ["$$this", {type: "indirect"}]}}}
      ]}
  }}
])

See how it works on the playground example