How do a merge multiple fields into a single array in an aggregation?

Question

I have data that has user primary keys in "user.pk", and "usertags.user.pk".

[
  {
    _id: ObjectId("62015250cb18d3cadb32c38e"),
    user: { pk: '7485070525' },
    usertags: [ { user: { pk: '334777488' } } ]
  },
  {
    _id: ObjectId("62015250cb18d3cadb32c38f"),
    user: { pk: '334777488' },
    usertags: [
      { user: { pk: '7485070525' } },
      { user: { pk: '271738610' } },
      { user: { pk: '31961021' } }
    ]
  }
]

I am looking to aggregate all this data into a single list of all distinct user primary keys, so the result for the data above would be something like { _id: null, user_pks: [7485070525, 334777488, 271738610, 31961021] }

I tried using the $group aggregation, but I can't figure out how to merge "user.pk", and "usertags.user.pk". Can anyone help me with that?

Edit: This is the closest I've gotten, which puts users and usertags in separate lists.

db.collection.aggregate([
  { $unwind: "$usertags" },
  { $group: { _id: null, users: { $push: "$user.pk" }, usertags: {$push: "$usertags.user.pk" } } },
])

1sina1 · Accepted Answer · 2022-02-08 05:23:17Z

1

you can do this:

test it here mongodb playground

db.collection.aggregate([
  {
    $group: {
      _id: null,
      user_pk: {
        $push: "$user.pk"
      },
      user_sub_pk: {
        $push: "$usertags"
      }
    },
    
  },
  // flattern array in user_sub_pk
  {
    $set: {
      user_sub_pk: {
        $reduce: {
          input: "$user_sub_pk",
          initialValue: [],
          in: {
            $concatArrays: [
              "$$value",
              "$$this"
            ],
            
          },
          
        },
        
      },
      
    },
    
  },
  {
    $project: {
      user_pks: {
        $concatArrays: [
          "$user_pk",
          "$user_sub_pk.user.pk"
        ],
        
      },
      
    },
    // remove duplicate items
    {
    "$project": {
      "user_pks": {
        "$setUnion": [
          "$user_pks",
          []
        ]
      }
    }
  }
    
  }
])

update: refactored by @zrbecker

db.collection.aggregate([
  {
    $group: {
      _id: null,
      users: {
        $push: "$user.pk"
      },
      usertags: {
        $push: "$usertags.user.pk"
      }
    }
  },
  {
    $project: {
      users: {
        $setUnion: [
          "$users",
          {
            $reduce: {
              input: "$usertags",
              initialValue: [],
              in: {
                $concatArrays: [
                  "$$value",
                  "$$this"
                ]
              }
            }
          }
        ]
      }
    }
  }
])

edited Feb 8, 2022 at 5:23

answered Feb 7, 2022 at 18:16

1sina1

1,0968 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

zrbecker Over a year ago

This is awesome, looks like the direction I need to go. However, something is wrong with the reduce stage. It doesn't grab the elements from the usertags in the first document. Here is a demonstration mongoplayground.net/p/TfYVJEbBgm7 I changed the usertag to 1234 and its not in the result.

zrbecker Over a year ago

This seems to have done it mongoplayground.net/p/KfzILnYQR0X

Collectives™ on Stack Overflow

How do a merge multiple fields into a single array in an aggregation?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related