CODEWITHSUNDEEP
c#databaselinqnosqlravendb
ZVenue
ZVenue

Reputation: 5027

RavenDB - Sift through documents and get count using index/query

I have a City document, Site document. City can have multiple sites. Site document has the city information in it. There are about 100 city documents and 10000 site documents in RavenDB

City Document:

{
    "CityCode": "NY",

    "CityName": "New York"
}

Site Document:

{
    "SiteName": "MOMA",

    "CityCode": "NY"
}

Objective is to get a list of all cities and the number of sites for each like...

City   Sites
NY     12
CH      33
BO      56
and so on....

I am doing this.

int countSites = session.Query<Site>()
                        .Count();


var SiteCityList = session.Query<Site>()
                          .Take(countSites)
                          .ToList()
                          .GroupBy(x => x.CityCode)
                          .OrderBy(x => x.Count())
                          .ToDictionary(x => x.Key, x => x.Count());

This does not give all the data in the ravendb. I get only 11 rows of count by site at any time and even the counts are not accurate. What I want is to get a list of all 100 cities and number of sites for each city (in 100s) as a list as shown above. Thanks for the help.

Upvotes: 2

Views: 1093

Answers (3)

Alex Klaus
Alex Klaus

Reputation: 8934

You've got 2 options:

  1. Create a Map/Reduce index and query against it
  2. Use Faceted (Aggregated) Search to query

To make a choice between the two, take into account that

  • A specially dedicated index is faster to query, but has a some footprint on the storage and performance for re-indexing on changed records (can be important if you need immediate consistency and wait for non-stale indexes).
  • Facet is simpler to use when you already have the fields covered in an existing index. I believe you understand importance of static indexes and already have some.

While using a Map/Reduce index is straightforward and already covered by Daniel's answer, I provide below an example of using Facets:

var query = DbSession.Query<Site_IndexModel, Site_ForList>();
List<FacetValue> facetResults = (await query
                                        .AggregateBy(builder => builder.ByField(s => s.CityCode ))
                                        .ExecuteAsync()
                                ).Single().Value.Values;
// Go through results, where each facetResult is { Range, Count } structure
return  from result in facetResults
        select new { CityCode = result.Range, Count = result.Count }

where

  • Site_ForList is an existing index for Site collection, which includes CityCode field
  • Site_IndexModel is the stored structure of Site_ForList index

Upvotes: 0

Matt Warren
Matt Warren

Reputation: 10291

If you want an alternative way, you can take a look at Faceted Search

It gives you slightly more flexibility than Map/Reduce, but will only work when you want a Count of items (which you do in your case).

Upvotes: 2

Daniel Lang
Daniel Lang

Reputation: 6839

Use a map/reduce index like this

public class CityCodeCount : AbstractIndexCreationTask<Site, CityCodeCount.ReduceResult>
{
    public class ReduceResult
    {
        public string CityCode { get; set; }
        public int Count { get; set; }
    }

    public CityCodeCount()
    {
        Map = sites => from site in sites
                        select new
                        {
                            site.CityCode,
                            Count = 1
                        };

        Reduce = results => from result in results
                            group result by result.CityCode
                            into g
                            select new
                            {
                                CityCode = g.Key,
                                Count = g.Sum(x => x.Count)
                            };

    }
}

Later then, you can query it easily.

var results = documentSession.Query<CityCodeCount.ReduceResult, CityCodeCount>()
    .ToList();

Upvotes: 3

Related Questions