Filter RavenDB Static Map / Reduce Index

Question

Script / context

• Raven 2.0 on RavenHQ
• The web application, therefore, async is preferred.

My application is a survey application. Each Survey has an array of Questions ; and vice versa, each Submission (individual response to the survey) has an array of Answers .

I have a static index that combines all the answers so that I can display a chart based on the answers (for example, for each question in each survey, how many people chose each option). This data is used to visualize, for example, a pie chart. This aggregation index (discussed in this question ) basically gives an object for each survey with a sum for each option.

Problem

I would like to filter out these aggregated values. Some of them are trivial because they are the fields in the result (for example, a SurveyId or QuestionId filter). However, I would also like to filter by sending date (from metadata) or by LocationId , which are fields in a separate Submissions , but clearly not in the aggregation results.

In other words, I need to be able to ask Raven about the results to ask a question for a specific LocationId or during this month.

Classes

Here's what one view looks like:

 { "SurveyId": 1, "LocationId": 1, "Answers": [ { "QuestionId": 1, "Values": [2,8,32], "Comment": null }, { "QuestionId": 2, "Values": [4], "Comment": "Lorem ipsum" }, ...more answers... ] } 

Currently, the result of aggregation is:

 public class Result { public int SurveyId { get; set; } // 1 public int QuestionId { get; set; } // 1 public int NumResponses { get; set; } // 576 public int NumComments { get; set; } // 265 public IList<KeyValuePair<int,int>> Values { get; set; } // [{Key:1, Value:264}, {Key:2, Value:163}, Key:4, Value:391}, ...] } 

Here's the aggregation index:

 Map = submissions => from submission in submissions from answer in submission.Answers select new { submission.SurveyId, answer.QuestionId, NumResponses = 1, NumComments = answer.Comment == null ? 0 : 1, Value = answer.Value.Select(x => new KeyValuePair<int, int>(x, 1)) }; Reduce = results => from result in results group result by new { result.SurveyId, result.QuestionId } into g select new Result { SurveyId = g.Key.SurveyId, QuestionId = g.Key.QuestionId, NumResponses = g.Sum(x => x.NumResponses), NumComments = g.Sum(x => x.NumComments), Value = g.SelectMany(x => x.Value) .GroupBy(x => x.Key) .Select(x => new KeyValuePair<int, int>(x.Key, x.Sum(y => y.Value))) }; 

I tend to conceptually "pass" these filters into the request, but from what I read, this will not work, because indexes are indexed (stored) asynchronously without separate send dates or LocationIds.

Does this mean that I will need to create an index of all the answers, and then index the aggregation index into this new AllAnswers index or something else? I was looking a bit for one index request to another, with no luck. Or is that what fields are used for

Any guidance is appreciated!

Answer 1

The index that you are now SurveyId together all the data, SurveyId and QuestionId . If you want it to be broken down by date or location, these are new indexes. You simply added the fields that you wanted to the map, included them in the grouping key and passed them as a result. Then you can easily request these keys.

If you have different grouping keys, you cannot consolidate this into a single index. You must have multiple indexes. For example, I can name the index that you have above Submission_TotalsBySurveyAndQuestion , and another index might be Submission_TotalsBySurveyAndQuestionPerLocation .

Think of it this way: right now you can include Where or OrderBy in your query, which goes against SurveyId or QuestionId , because they group keys in your index. If you want to filter or sort by LocationId , this should be included.

One word of caution, you said:

I would also like to filter by sending date (from metadata)

The only date that RavenDB gives you in metadata (the default) is the Last-Modified date. Any editing of the document will update this. Therefore, if the filing date is important to you, then you probably should keep it in your own property.