RavenDB Map / Reduce / Transform on variable length nested arrays

Question

I am new to RavenDB and still love it. I have one remaining index for my project.

Problem

I have thousands of responses to polls (ie, “ Submissions ”), and each view has many answers to specific questions (ie, “ Answers ”), and each answer has a set of parameters that have been selected (i.e. . " Values ").

Here's what one Submission looks like:

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

Additional problem: I have the ability to filter SurveyId, LocationId, QuestionId, Creation Date. As far as I understand, this was done at the time of the request ... I just need to make sure that these properties are present as a result of the conversion (or is it the result of reduction? Or both?). If I'm right, then this is not a problem.

Desired Result

We need one object for each survey, which gives the sum of each option. I hope he will explain:

 [ { SurveyId: 1, QuestionId: 1, NumResponses: 976, NumComments: 273, Values: { "1": 452, // option 1 selected 452 times "2": 392, // option 2 selected 392 times "4": 785 // option 4 selected 785 times } }, { SurveyId: 1, QuestionId: 2, NumResponses: 921, NumComments: 46, Values: { "1": 325, "2": 843, "4": 119, "8": 346, "32": 524 } }, ... ] 

My attempt

I am not very far away and I think this post guides me on the right path, but it does not help me with the list of values, I searched and searched, but I can not find any direction for what to do with the nested array. Here is what I still have:

MAP:

 from submission in docs.Submissions from answer in submission.Answers where answer.WasSkipped != true && answer.Value != null select new { SubmissionDate = submission["@metadata"]["Last-Modified"], SurveyId = submission.SurveyId, LocationId = submission.LocationId, QuestionId = answer.QuestionId, Value = answer.Value } 

REDUCE:

 ?? 

Transform:

 from result in results from answer in result.Answers where answer.WasSkipped != true && answer.Value != null select new { SubmissionDate = result["@metadata"]["Last-Modified"], SurveyId = result.SurveyId, LocationId = result.LocationId, QuestionId = answer.QuestionId, Value = answer.Value } 

For what it's worth, it's hosted on RavenHQ.

It has been so long that I have been working on it and cannot understand. Any help in getting me to the desired result is greatly appreciated!

Answer 1

Assuming your C # classes are as follows:

 public class Submission { public int SurveyId { get; set; } public int LocationId { get; set; } public IList<Answer> Answers { get; set; } } public class Answer { public int QuestionId { get; set; } public int[] Values { get; set; } public string Comment { get; set; } } 

If you are using RavenDB 2.5.2637 or higher, now you can use the dictionary result type:

 public class Result { public int SurveyId { get; set; } public int QuestionId { get; set; } public int NumResponses { get; set; } public int NumComments { get; set; } public Dictionary<int, int> Values { get; set; } } 

If you run anything earlier (including version 2.0), then you cannot use the dictionary, but instead you can use IList<KeyValuePair<int,int>> .

Here is the index:

 public class TestIndex : AbstractIndexCreationTask<Submission, Result> { public TestIndex() { 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, Values = answer.Values.ToDictionary(x => x, x => 1) //Values = answer.Values.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 { g.Key.SurveyId, g.Key.QuestionId, NumResponses = g.Sum(x => x.NumResponses), NumComments = g.Sum(x => x.NumComments), Values = g.SelectMany(x => x.Values) .GroupBy(x => x.Key) .ToDictionary(x => x.Key, x => x.Sum(y => y.Value)) //.Select(x => new KeyValuePair<int, int>(x.Key, x.Sum(y => y.Value))) }; } } 

(Conversion step is not required.)

If you cannot use 2.5.2637 or higher, replace .ToDictionary lines with comment lines just below them and use IList<KeyValuePair<int,int>> in the result class.

The fix for using dictionaries in map / reduce was based on this issue , which your mail helped identify. Thanks!