Mongodb array concatenation

Question

When querying mongodb, is it possible to process ("project") the result so as to perform array concatenation? I actually have 2 different scenarios:

(1) Arrays from different fields:, e.g:

Given:
{companyName:'microsoft', managers:['ariel', 'bella'], employees:['charlie', 'don']}
{companyName:'oracle',   managers:['elena', 'frank'], employees:['george', 'hugh']}

I'd like my query to return each company with its 'managers' and 'employees' concatenated:
{companyName:'microsoft', allPersonnel:['ariel', 'bella','charlie', 'don']}
{companyName:'oracle',   allPersonnel:['elena', 'frank','george', 'hugh']}

(2) Nested arrays:, e.g.:

Given the following docs, where employees are separated into nested arrays (never mind why, it's a long story):
{companyName:'microsoft', personnel:[ ['ariel', 'bella'], ['charlie', 'don']}
{companyName:'oracle',   personnel:[ ['elena', 'frank'], ['george', 'hugh']}

I'd like my query to return each company with a flattened 'personal' array:
{companyName:'microsoft', allPersonnel:['ariel', 'bella','charlie', 'don']}
{companyName:'oracle',   allPersonnel:['elena', 'frank','george', 'hugh']}

I'd appreciate any ideas, using either 'find' or 'aggregate' Thanks a lot :)

Neil Lunn · Accepted Answer · 2017-06-28 03:56:53Z

Of Course in Modern MongoDB releases we can simply use $concatArrays here:

db.collection.aggregate([
  { "$project": {
    "companyNanme": 1,
    "allPersonnel": { "$concatArrays": [ "$managers", "$employees" ] }
  }}
])

Or for the second form with nested arrays, using $reduce in combination:

db.collection.aggregate([
  { "$project": {
    "companyName": 1,
    "allEmployees": {
      "$reduce": {
        "input": "$personnel",
        "initialValue": [],
        "in": { "$concatArrays": [ "$$value", "$$this" ] }
      }
    }
  }}
])

There is the $setUnion operator available to the aggregation framework. The constraint here is that these are "sets" and all the members are actually "unique" as a "set" requires:

db.collection.aggregate([
    { "$project": {
        "companyname": 1,
        "allPersonnel": { "$setUnion": [ "$managers", "$employees" ] }
    }}
])

So that is cool, as long as all are "unique" and you are in singular arrays.

In the alternate case you can always process with $unwind and $group. The personnel nested array is a simple double unwind

db.collection.aggregate([
   { "$unwind": "$personnel" },
   { "$unwind": "$personnel" },
   { "$group": {
       "_id": "$_id",
       "companyName": { "$first": "$companyName" },
       "allPersonnel": { "$push": { "$personnel" } }
   }}
])

Or the same thing as the first one for versions earlier than MongoDB 2.6 where the "set operators" did not exist:

db.collection.aggregate([
    { "$project": {
        "type": { "$const": [ "M", "E" ] },
        "companyName": 1,
        "managers": 1,
        "employees": 1
    }},
    { "$unwind": "$type" },
    { "$unwind": "$managers" },
    { "$unwind": "$employees" },
    { "$group": {
        "_id": "$_id",
       "companyName": { "$first": "$companyName" },
       "allPersonnel": {
           "$addToSet": {
               "$cond": [
                   { "$eq": [ "$type", "M" ] },
                   "$managers",
                   "$employees"
               ]
           }
       }
    }}
])

Collectives™ on Stack Overflow

Mongodb array concatenation

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related