Group two fields

[Col_Dig]

Hello. I have a collection with documents like this

Document one

{
   "_id":"ObjectId",
   "users":[
      {
         "region":"A""value":"OK"
      }
   ]
}

Document two

{
   "_id":"ObjectId",
   "info":[
      {
         "region":"A""notes":"CRITICAL"
      }
   ]
}

I need to group all of them based on the region field.

Output example

{
   "region":"A",
   "users":[
      {
         "region":"A",
         "value":"OK"
      }
   ],
   "info":[
      {
         {
            "region":"A""notes":"CRITICAL"
         }
      }
   ]
}

[Pavel_Duchovny]

Hi @Col_Dig ,

In order to help you can you clarify this format?

{
         "region":"A""notes":"CRITICAL"
      }

Is it a single field with quote and colon inside?

Or two fields:

{
         "region":"A",
         "notes":"CRITICAL"
      }

Thanks
Pavel

[Col_Dig]

Hello! It’s like the second one

Here’s a more detailed example, I don’t have problem with any other output but I need to group them by the region field in both of them.
Thanks!

Object one

{
    users: [
        {region: 'A', value: 'OK'}
    ],
    info: [
        {region: 'C', notes: 'CRITICAL'}
    ]
}

Object two

{
    users: [
        {region: 'A', value: 'ACCEPTED'},
        {region: 'C', value: 'PENDING'}
    ],
    info: [
        {region: 'A', notes: 'LOOKS FINE'},
        {region: 'C', notes: 'NOT GOOD'}
    ]
}

Output

{
    "region": "A",
    "users": [
        {region: 'A', value: 'OK'},
        {region: 'A', value: 'ACCEPTED'}
    ],
    "info": [
        {region: 'A', notes: 'LOOKS FINE'},
    ]
},
{
    "region": "C",
    "users": [
        {region: 'C', value: 'PENDING'}
    ],
    "info": [
        {region: 'C', notes: 'NOT GOOD'}
    ]
}

[Pavel_Duchovny]

Hi @Col_Dig ,

Sorry for the delay in response.

I believe with $group and $addToSet aggregation it can be done in one query.

Having said that I will try to test and provide you with a detailed result today.

Thanks
Pavel

[Pavel_Duchovny]

Hi @Col_Dig ,

The following query will do the job. Its not perfect but works:

db.users.aggreagate([{
// Unwind the users to locate each region
$unwind: {
  path: "$users"
}}, 
// group per region and filter only relevant info for each region
{$group: {
  _id: "$users.region",
  users: {
    $addToSet : "$users"
  },

  info : {$addToSet : { $filter : { input : "$$ROOT.info", as : "currInfo",
    cond : { $eq : ["$$currInfo.region", "$$ROOT.users.region"]}
      }}

  }
}},
// Reshape the output
 {$project: {
  "_id" : 0,
  region: "$_id",
  users : 1,
  info :  {$filter : {input : "$info", as : "curr" , cond :{$ne : ["$$curr", []]}}}
}}]
)

Thanks,
Pavel

[Col_Dig]

Thanks! It worked great