Cartesian product in aggregation pipeline

[Shiv]

Below is the sample data on MongoDB version 3.5.7

db.testcol.find();

{
 "data" : [
  [
   {
    "movie" : "starwars",
    "showday" : "monday"
   },
   {
    "movie" : "batman",
    "showday" : "thursday"
   },
   {
    "movie" : "sleepless",
    "showday" : "tuesday"
   }
  ],
  [
   {
    "actor" : "angelina",
    "location" : "new york"
   },
   {
    "actor" : "jamie",
    "location" : "california"
   },
   {
    "actor" : "mcavoy",
    "location" : "arizona"
   }
  ]
 ]
}

This is the working javascript implementation.

function cartesianProduct(arr)
{
    return arr.reduce(function(a,b){
        return a.map(function(x){
            return b.map(function(y){
                return x.concat(y);
            })
        }).reduce(function(a,b){ return a.concat(b) },[])
    }, [[]])
}

The closest MongoDB aggregation code for above js implementation which outputs empty array.

db.testcol.aggregate({
 $project: {
  cp: {
   $reduce: {
    input: "$data",
    initialValue: [],
    in: {
     $let: {
      vars: {
       currentr: "$$this",
       currenta: "$$value"
      },
      in:{
       $reduce: {
        input: {
         $map: {
          input: "$$currenta",
          as: "a",
          in: {
           $map: {
            input: "$$currentr",
            as: "r",
            in: {
             $mergeObjects: ["$$a", "$$r"]
            }
           }
          }
         }
        },
        initialValue: [],
        in: {
         $concatArrays: ["$$value", "$$this"]
        }
       }
      }
     }
    }
   }
  }
 }
});

Output:

{ "_id" : ObjectId("592cce1cc23b82abed7f7b97"), "cp" : [ ] }

I have narrowed down the reason being specification for reduce function in js vs MongoDB.

From reduce docs here https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/Reduce?v=example#Description

 If no initialValue is provided, then accumulator will be equal to the first value in the array, and currentValue will be equal to the second.

The MongoDB behavior is not mentioned in the docs (may be because it is not a special condition), but it looks like the $$value is not set and $$this will be first value in the array.

Can you explain if this is indeed the case ?

Okay now to  accommodate the difference in specification, I have set the initial value to the first element in the array and added a conditional expression to check accumulator value ($$value) is same as current value ($$this) and return. 

Can you advise if this is right way to go about it ? 

Something like 

db.testcol.aggregate({
 $project: {
  cp: {
   $reduce: {
    input: "$data",
    initialValue: {
     $arrayElemAt: ["$data", 0] // Set the initial value to the first element of the arrays.
    },
    in: {
     $let: {
      vars: {
       currentr: "$$this", // Current processing element
       currenta: "$$value" // Current accumulated value
      },
      in: {
       $cond: [{ // Conditional expression to return the accumulated value as initial value for first element
        $eq: ["$$currentr", "$$currenta"]
       },
       "$$currenta",
       { // From second element onwards prepare the cartesian product
        $reduce: {
         input: {
          $map: {
           input: "$$currenta",
           as: "a",
           in: {
            $map: {
             input: "$$currentr",
             as: "r",
             in: {
              $mergeObjects: ["$$a", "$$r"] // Merge accumulated value with the current processing element
             }
            }
           }
          }
         },
         initialValue: [],
         in: {
         $concatArrays: ["$$value", "$$this"] // Reduce the merged values which will be used as accumulator for next element
         }
        }
       }]
      }
     }
    }
   }
  }
 }
});

Other implementation is to use $setUnions instead of $cond & $concatArrays.

db.testcol.aggregate({
 $project: {
  cp: {
   $reduce: {
    input: "$data",
    initialValue: {
     $arrayElemAt: ["$data", 0]
    },
    in: {
     $let: {
      vars: {
       currentr: "$$this",
       currenta: "$$value"
      },
      in:{
       $reduce: {
        input: {
         $map: {
          input: "$$currenta",
          as: "a",
          in: {
           $map: {
            input: "$$currentr",
            as: "r",
            in: {
             $mergeObjects: ["$$a", "$$r"]
            }
           }
          }
         }
        },
        initialValue: [],
        in: {
         $setUnion: ["$$value", "$$this"]
        }
       }
      }
     }
    }
   }
  }
 }
});

Appreciate the reply. Let me know if you have need more details.

More details can be found here 

https://stackoverflow.com/questions/43819186/c-sharp-mongodb-cartesian-product-of-multiple-object-array-documents/

Thanks,

Shiv

[Asya Kamsky]

I’m not sure what your question is, but you can just set initialValue to empty array and it should simplify your pipeline significantly.

Asya

--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.
 
For other MongoDB technical support options, see: https://docs.mongodb.com/manual/support/
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user+unsubscribe@googlegroups.com.
To post to this group, send email to mongod...@googlegroups.com.
Visit this group at https://groups.google.com/group/mongodb-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/05a824ad-fd4a-486c-9cb0-3a9b83902bf1%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Asya Kamsky
Lead Product Manager
MongoDB
Download MongoDB - mongodb.org/downloads
We're Hiring! - https://www.mongodb.com/careers

[Shiv]

Thank you for responding. Sorry for not being clear.

I believe when you say setting initialValue to empty array you mean the below query, but this yields empty array. Can you advise how to fix the query to output cartesian product ?

Thanks 

Shiv

To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user...@googlegroups.com.

To post to this group, send email to mongod...@googlegroups.com.
Visit this group at https://groups.google.com/group/mongodb-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/05a824ad-fd4a-486c-9cb0-3a9b83902bf1%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Asya Kamsky]

Ah, sorry, I forgot you were using this array in a way that eventually merges it with other array of objects.

Does it work better when you try empty object in array for initial value:  [ { } ] - array with a single empty object?

I think the response on SO was very close, just didn’t quite do the right thing with the first element of the data array.

The right solution is to emulate exactly what the JS implementation does, make initialValue the first element of the data array, but make the *input* all but the first element (using $slice).

Asya

To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user+unsubscribe@googlegroups.com.

To post to this group, send email to mongod...@googlegroups.com.
Visit this group at https://groups.google.com/group/mongodb-user.

To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/a385f7e1-7584-44ec-ac57-e748eebb7a38%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Shiv]

Always simple and elegant. Thank you for providing your valuable inputs. 

Both the fixes you've mentioned worked fine and exactly what I was looking for.

Here is the complete aggregation pipeline.

db.testcol.aggregate({
 $project: {
  cp: {
   $reduce: {
    input: {$slice:["$data", 1, {$subtract:[{$size:"$data"},1]}]},
    initialValue: {$arrayElemAt:["$data",0]},

    in: {
     $let: {
      vars: {
       currentr: "$$this",
       currenta: "$$value"
      },
      in:{
       $reduce: {
        input: {
         $map: {
          input: "$$currenta",
          as: "a",
          in: {
           $map: {
            input: "$$currentr",
            as: "r",
            in: {
             $mergeObjects: ["$$a", "$$r"]
            }
           }
          }
         }
        },
        initialValue: [],
        in: {
         $concatArrays: ["$$value", "$$this"]
        }
       }
      }
     }
    }
   }
  }
 }
});

Thanks again!

- Shiv

To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/a385f7e1-7584-44ec-ac57-e748eebb7a38%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.