Map reduces in RavenDb more than 2 collections with a children's collection

Question

I have two different types of objects stored in RavenDb, which are parent / type relationships like this in JSON:

Account/1 { "Name": "Acc1", } Items/1 { "Account": "Account/1", "Value" : "100", "Tags": [ "tag1", "tag2"] } Items/2 { "Account": "Account/1", "Value" : "50", "Tags": [ "tag2"] } 

Please note that I do not want to store them in the same document, since an account can have thousands of elements.

I am trying to write a map / reduce index that will return me something like:

 { "Account": "Acc1", "TagInfo": [ { "TagName" : "tag1", "Count" : "1", //Count of all the "tag1" occurrences for acc1 "Value" : "100" //Sum of all the Values for acc1 which are tagged 'tag1' }, { "TagName" : "tag2", "Count" : "2", //Two items are tagged "tag2" "Value" : "150" }] } 

i.e. a list of all individual tag names along with each number and their value.

I think I need to use a multi-map to map the Account and Items collections together, but I cannot understand the abbreviation part to create the “TagInfo” result part.

Is this possible, or am I modeling all of this incorrectly in Raven?

EDIT:

The class that I want to extract from this query will look something like this:

 public class QueryResult { public string AccountId {get;set;} public TagInfo Tags {get;set;} } public class TagInfo { public string TagName {get;set;} public int Count {get;set;} public int TotalSum {get;set;} } 

Answer 1

Ok, so I figured out a way to do this in an acceptable way, which is based on Daniel's answer, so I will write it down here for future travelers (maybe myself!).

I gave up trying to return one result for each account, to one result for each combination of accounts / tags, so the index should change as follows (note that group by in reduce has 2 properties):

 public class TagsWithCountAndValues : AbstractIndexCreationTask<Item, TagsWithCountAndValues.ReduceResult> { public class ReduceResult { public string AccountId { get; set; } public string AccountName { get; set; } public string TagName { get; set; } public int TagCount { get; set; } public int TagValue { get; set; } } public TagsWithCountAndValues() { Map = items => from item in items from tag in item.Tags select new ReduceResult { AccountId = item.AccountId, TagName = tag, TagCount = 1, TagValue = item.Value }; Reduce = results => from result in results where result.TagName != null group result by new {result.AccountId, result.TagName} into g select new ReduceResult { AccountId = g.Key.AccountId, TagName = g.Key.TagName, TagCount = g.Sum(x => x.TagCount), TagValue = g.Sum(x => x.TagValue), }; TransformResults = (database, results) => from result in results let account = database.Load<Account>(result.AccountId) select new ReduceResult { AccountId = result.AccountId, AccountName = account.Name, TagName = result.TagName, TagCount = result.TagCount, TagValue = result.TagValue, }; } } 

As before, requesting this is simple:

 var results = session .Query<TagsWithCountAndValues.ReduceResult, TagsWithCountAndValues>() .ToList(); 

The result of this can then be converted to the object that I initially needed in the LINQ query in memory. At this stage, the number of results that can be returned will be relatively small, so doing this on the client side is easily acceptable. LINQ statement:

 var hierachicalResult = from result in results group new {result.TagName, result.TagValue} by result.AccountName into g select new { Account = g.Key, TagInfo = g.Select(x => new { x.TagName, x.TagValue, x.TagCount }) }; 

This gives us one object for each account with a child list of TagInfo objects - one for each unique tag.

Answer 2

You cannot use the Multi Map / Reduce index for this because you need one map in the tags and another in the account. They do not have a common property, so there cannot be several cards / abbreviations.

However, you can use TransformResult instead. Here's how to do it:

 public class Account { public string Id { get; set; } public string Name { get; set; } } public class Item { public string Id { get; set; } public string AccountId { get; set; } public int Value { get; set; } public List<string> Tags { get; set; } } public class TagsWithCountAndValues : AbstractIndexCreationTask<Item, TagsWithCountAndValues.ReduceResult> { public class ReduceResult { public string AccountId { get; set; } public string AccountName { get; set; } public string Tag { get; set; } public int Count { get; set; } public int TotalSum { get; set; } } public TagsWithCountAndValues() { Map = items => from item in items from tag in item.Tags select new { AccountId = item.AccountId, Tag = tag, Count = 1, TotalSum = item.Value }; Reduce = results => from result in results group result by result.Tag into g select new { AccountId = g.Select(x => x.AccountId).FirstOrDefault(), Tag = g.Key, Count = g.Sum(x => x.Count), TotalSum = g.Sum(x => x.TotalSum) }; TransformResults = (database, results) => from result in results let account = database.Load<Account>(result.AccountId) select new { AccountId = result.AccountId, AccountName = account.Name, Tag = result.Tag, Count = result.Count, TotalSum = result.TotalSum }; } } 

You may later request the following:

 var results = session.Query<TagsWithCountAndValues.ReduceResult, TagsWithCountAndValues>() .Where(x => x.AccountId == "accounts/1") .ToList();