# Aggregation with distinct null elements

**URL:** <https://discuss.elastic.co/t/aggregation-with-distinct-null-elements/225393>\
**Category:** Elasticsearch\
**Created:** [March 27, 2020, 11:33am UTC](https://discuss.elastic.co/t/aggregation-with-distinct-null-elements/225393 "2020-03-27T11:33:59Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Ekaterina\_Nekrasova](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ekaterina_nekrasova/32/46505_2.png) [@Ekaterina\_Nekrasova](https://discuss.elastic.co/u/Ekaterina_Nekrasova)\
**Post date:** [March 27, 2020, 11:33am UTC](https://discuss.elastic.co/t/aggregation-with-distinct-null-elements/225393/1 "2020-03-27T11:33:59Z")

</div>

Hello everybody !  
I have PersonType with groupId field, which can be null, or even doesn't exist

```auto
{
Id: 1
Name : ToTo,
GroupId: 1
}
{
Id: 2
Name : TaTo,
GroupId: 2
}
{
Id: 3
Name : ToTa,
GroupId: null
}
{
Id: 4
Name : ToTo
}
{
Id: 5
Name : TaTa,
GroupId: 2
}

```

I need to sort them by Name and aggregate by GroupId, where if GroupId is null or doesn't exist it should be see as different group for each null  
so for my example i will have :  
2, 3, 1, 4

How can i do this, is it possible ?

---

<div class="post-metadata">

**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\
**Post date:** [March 30, 2020, 2:49pm UTC](https://discuss.elastic.co/t/aggregation-with-distinct-null-elements/225393/2 "2020-03-30T14:49:01Z")

</div>

Hey

Would the [missing value](https://www.elastic.co/guide/en/elasticsearch/reference/7.6/search-aggregations-bucket-terms-aggregation.html#_missing_value_16) features of the terms agg help here?

--Alex

---

<div class="post-metadata">

**Author:** ![Ekaterina\_Nekrasova](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ekaterina_nekrasova/32/46505_2.png) [@Ekaterina\_Nekrasova](https://discuss.elastic.co/u/Ekaterina_Nekrasova)\
**Post date:** [March 30, 2020, 3:23pm UTC](https://discuss.elastic.co/t/aggregation-with-distinct-null-elements/225393/3 "2020-03-30T15:23:20Z")

</div>

Hi ! Thanks for your reply =)  
No it's not good solution for me, because i need distinct persons when they have null group, but in your solution i will aggregate 3 persons with null groupId into one bucket .

I found solution. i use the script in aggregation, which choose GroupId or -Id, like that i can cover all documents with or without GroupId

```auto
private readonly string _customGroupIdScript = "if (doc['groupId'].empty || doc['groupId'].value == null || doc['groupId'].value == 0)"
                                                       + "{ return -(Long.parseLong(doc['id'].value)); } "
                                                       + "else { return doc['groupId'].value; }";

```

```auto
body.Aggregations(
                            aggs => aggs
                                    .Terms(
                                        "groupId",
                                        gr => gr
                                              .Script(_customGroupIdScript)
                                              .Order(
                                                  new TermsOrder
                                                  {
                                                      Key = "current_score",
                                                      Order = parameters.Sort.Order == Order.ASC
                                                                  ? appliedSort ? SortOrder.Ascending : SortOrder.Descending
                                                                  : SortOrder.Descending
                                                  })
                                              .Size(parameters.Start + parameters.Length)
                                              .Aggregations(
                                                  aggss => aggss
                                                           .Max("current_score", tt => tt.Script("_score"))
                                                           .TopHits(
                                                               "first_elementid", tt => tt
                                                                                        .Source(s => s.Includes(f => f.Field("id")))
                                                                                        .Size(1))))
                                    .Cardinality("totalDistinct", c => c.Script(_customGroupIdScript))
                        );

```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [April 27, 2020, 3:23pm UTC](https://discuss.elastic.co/t/aggregation-with-distinct-null-elements/225393/4 "2020-04-27T15:23:22Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.