sql distinct子句-嵌套c#

gzjq41n4  于 2021-06-14  发布在  ElasticSearch
关注(0)|答案(2)|浏览(302)

我需要使用nest返回文档中的所有categories distincts(不重复任何内容)。
在sql中,它如下所示:

SELECT DISTINCT Category  
  FROM Log  
  ORDER BY Category ASC

在elasticsearch中,我是这样做的:

GET log/_search
{
  "size":"0",
  "aggs" : {
  "alias_category" : {
  "terms" : { "field" : "category.keyword" }
  }
  }
}

我怎么能用鸟巢做到这一点?

public ICollection<string> SelectAllCategoriesDistinct(ElasticClient client)
    {
        var searchResponse = client.Search<LogElasticSearch>(s => s
                                        .Query(q => q
                                            .Terms(t => t
                                                .Field(f => f.Category)
                                            )
                                        )
                                     );
        return (ICollection<string>)searchResponse;
    }
wooyq4lh

wooyq4lh1#

我找到了一个办法。不是一个优雅的方式,但我发现在弹性网站(https://discuss.elastic.co/t/c-nest-best-way-of-accessing-properties-of-iaggregate-object/85384/2)我是这样做的:

public ICollection<string> SelectAllCategoriesDistinct(ElasticClient client)
    {
        var searchResponse = 
            client.Search<LogElasticSearch>(s => s
                .Size(0)
                .Aggregations(agg => agg
                    .Terms("categories", t => t
                        .Field("category.keyword")
                    )                  
                )
            );

        var aggregation = searchResponse.Aggregations.Values;
        var listOfCategories = new List<string>();

        if (searchResponse.Aggregations.Values.FirstOrDefault().GetType() == typeof(BucketAggregate))
        {
            foreach (IBucket bucket in ((BucketAggregate)aggregation.FirstOrDefault()).Items)
            {
                if (bucket.GetType() == typeof(KeyedBucket<object>))
                {
                    var valueKey = ((KeyedBucket<object>)bucket).Key;
                    listOfCategories.Add(valueKey.ToString());
                }
            } 
        }

        return listOfCategories.OrderBy(c => c).ToList();
    }

如果有人知道更好的方法,帮助我提高,但这样就达到了目标。

avkwfej4

avkwfej42#

我将使用复合聚合来获取所有术语。与术语聚合不同,复合聚合支持分页,因此如果有很多术语要获取,则可以发出多个请求

private static void Main()
{
    var pool = new SingleNodeConnectionPool(new Uri("http://localhost:9200"));
    var settings = new ConnectionSettings(pool); 
    var client = new ElasticClient(settings);

    var categories = new List<string>();

    GetAllCategories(client, categories);

    // do something with the categories
    foreach(var category in categories)
        Console.WriteLine(category);
}

private static void GetAllCategories(IElasticClient client, List<string> categories, CompositeKey after = null)
{
    // number of terms to fetch in each request
    var size = 10_000;

    var response = client.Search<LogElasticSearch>(s => s
        .Size(0)
        .Aggregations(a => a
            .Composite("categories", c => c
                .After(after)
                .Size(size)
                .Sources(so => so
                    .Terms("category", t => t
                        .Field("category.keyword")
                        .Order(SortOrder.Ascending)
                    )
                )
            )
        )
    );

    var compositeAgg = response.Aggregations.Composite("categories");
    var buckets = compositeAgg.Buckets;

    foreach (var bucket in buckets)
    {
        if (bucket.Key.TryGetValue("category", out string category))
        {
            categories.Add(category);
        }
    }

    // there may be more
    if (buckets.Count == size)
    {
        GetAllCategories(client, categories, compositeAgg.AfterKey);
    }
}

相关问题