CODEWITHSUNDEEP
mongodb
Max Frai
Max Frai

Reputation: 64326

Get unique array elements in MongoDB by "master" element

I have mongodb rows with array element which looks like this:

{"data" : [1, 111]}
{"data" : [222, 1]}
{"data" : [1, 333]}
{"data" : [2, 444]}

How to get unique array elements by "master" element. So for example "master" element is 1 I should get result: [111, 222, 333] and not 444, because that array does not contain 1. If master element would be 2, the result should be: [444]

I tried something like this aggregation. Is it correct? Are there any performance issues? What indecies should be on table to make it fast?

[
  {$match: {"data": 1}},
  {$project : {a : '$data'}},
  {$unwind: '$a'},
  {$group: {_id: 'a', items: {$addToSet: '$a'}}}
]

Upvotes: 1

Views: 60

Answers (2)

Takis
Takis

Reputation: 8705

I think this works also

Test code here

  • keeps only the arrays that contain the master key
  • unwind them
  • group by {"_id" 1} is like group by null, all make it true, just added to have the master key as _id (on the group $$REMOVE system variable is used to not add the master key)

Query (where you see 1 put your master key, or a variable)

db.collection.aggregate([
  {
    "$match": {
      "data": 1
    }
  },
  {
    "$unwind": {
      "path": "$data"
    }
  },
  {
    "$group": {
      "_id": 1,
      "members": {
        "$addToSet": {
          "$cond": [
            {
              "$ne": [
                "$data",
                1
              ]
            },
            "$data",
            "$$REMOVE"
          ]
        }
      }
    }
  }
])

Upvotes: 1

NeNaD
NeNaD

Reputation: 20354

You can use Aggregation framework:

  • $match to filter all documents that have "master" key in the "data" array.
  • $group to concatenate "data" arrays of all documents in one property called "result" and $filter to filter our "master" element from "data" arrays. ("result" will be an array that will have all documents "data" arrays as elements).
  • $reduce with $concatArrays to concatenate all "data" arrays inside "result" property.
db.collection.aggregate([
  {
    "$match": {
      data: 1
    }
  },
  {
    "$group": {
      "_id": null,
      result: {
        $addToSet: {
          "$filter": {
            "input": "$data",
            "cond": {
              "$ne": [
                "$$this",
                1
              ]
            }
          }
        }
      }
    }
  },
  {
    "$project": {
      result: {
        $reduce: {
          input: "$result",
          initialValue: [],
          in: {
            $concatArrays: [
              "$$value",
              "$$this"
            ]
          }
        }
      }
    }
  }
])

Be aware that the "master" element has to be dynamically populated in first stage for $match pipeline, as well as in the second stage when performing filtering with $filter operator.

Here is the working example: https://mongoplayground.net/p/EtYwOqAE-PE

Upvotes: 1

Related Questions