Sample Header Ad - 728x90

Finding the distinct values in an array of documents

1 vote
1 answer
67 views
I've got a database full of documents which each contain a collection of transactions:
[
  {
    "key": 1,
    "data": [
      {
        "trans": 1,
        "uid": 1
      },
      {
        "trans": 2,
        "uid": 1
      }
    ]
  },
  {
    "key": 2,
    "data": [
      {
        "trans": 3,
        "uid": 1
      },
      {
        "trans": 4,
        "uid": 2
      }
    ]
  }
]
I want to create a new field in each of the main documents, which has the unique values of the uid field. I can get partway there using $map:
db.collection.aggregate([
  {
    "$set": {
      "uid": {
        "$map": {
          "input": "$data",
          "as": "trans",
          "in": "$$trans.uid"
        }
      }
    }
  }
])
This gives me:
[
  {
    "key": 1,
    "uid": [1,1],
    "data": [
      {
        "trans": 1,
        "uid": 1
      },
      {
        "trans": 2,
        "uid": 1
      }
    ]
  },
  {
    "key": 2,
    "uid": [1, 2],
    "data": [
      {
        "trans": 3,
        "uid": 1
      },
      {
        "trans": 4,
        "uid": 2
      }
    ]
  }
]
This is close, but I can't seem to figure out the last step, I want to use only the unique values, so the uid for the first document should be `, not [1, 1]`. The distinct() function works across collections, not single documents. I would think that $addToSet would work, but it doesn't operate on arrays, only on the output of $group. I also looked at trying to create a $reduce specification using $setUnion, but I don't know how to promote my numeric value into an array. I can use the $unwind stage with grouping by _id to get the right values for the new field, but I can't figure out how to attach them back to the original objects.
Asked by ralmond (13 rep)
Nov 8, 2024, 11:26 PM
Last activity: Nov 9, 2024, 01:10 AM