A way to bring facet results together based on common id

Viewed 11

I'm doing a mongodb aggregation with two facets. Each facet is a different operation performed on the same collection. Each facet's results had two fields per object; the id and the operation result. I want to combine each facet's results based on the common id.

The desired result is like this:

[
 {
  "id":"1",
  "bind":"xxx",
  "pres":"xxx"
 },
 {
  "id":"2",
  ......
 }
]

I would like unfound areas to be zero or not be included if that is supported.

I've started with

const combined_agg = [
  {
    "$facet":{
        "bind":opp_bind,
        "pres":opp_pres,
      }
  }

Where the two opp are the variables for the two operations. The above gives me:

[
 {
  "bind":
   [
    {"binding":6,"id":"xxxx"},
    ....
   ],
  "pres":
   [
    {"presenting":4,"id":"xxxx"},
    ....
   ]
 }
]

From here, I am running into trouble.

I have tried to concatenate the arrays with

{
   "$project":{"result":{"$concatArrays":["$bind","$pres"]}}
}

which gives me one object with one large array. I tried to $unwind that large array so I objects are at the root but unwind only gives me the first 20 items of the array.

I tried using $group within the result array, but that gives me an id field with an array of all the ids and two other fields with arrays of their values.

{
   "$group":{
     "_id":"$result.id",
     "fields":{
       "$push":{"bind":"$result.bind","pres":"$result.pres"}
     }
   }
 }

I don't know how to separate them out so I can recombine them. I also saw some somewhat similar problems using map but I couldn't wrap my head around it.

1 Answers

I was able to figure out how to do it. I used lookup with a pipeline to get the right format.

Lookup added the result to every object of the original query. Then I used project and filter to find the correct value from the second query. Then I used addFields and arrayElementAt to get the value I wanted along with another project to get only the values I needed. It wasn't very pretty though.

Related