Thanks for the reply Jason
I think my use of arrays was confusing the question and isn’t actually required to answer my question. I can simplify my data and swap the arrays for integer values and my question still stands. Take this data for example
{ animals : { cats: 3, dogs: 3 }}
{ animals : { cats: 2, fish: 1 }}
{ animals : { dogs: 2 }}
I would like my query to calculate the average number of cats, dogs and fish across all documents. If a given document doesn’t have a value for a specific animal I would like 0 to be used in place. In the above document the average number of cats would be calculated as ( 3 + 2 + 0 ) / 3. Basically I would like the above documents to be expanded to something like this automatically in the query.
{ animals : { cats: 3, dogs: 3, fish: 0 }}
{ animals : { cats: 2, fish: 1, dogs: 0 }}
{ animals : { dogs: 2, cats: 0, fish: 0 }}
I also need to be able to calculate the average number of all animal types without having to explicitly specify each type of animal. In my case I have 500+ different ‘types’ of objects so having to specify each one in the query isn’t really feasible.
Does that make sense?
Thanks-
John