# How to union fields to get distinct count?

**URL:** <https://discuss.elastic.co/t/how-to-union-fields-to-get-distinct-count/57585>\
**Category:** Elasticsearch\
**Created:** [August 9, 2016, 11:02am UTC](https://discuss.elastic.co/t/how-to-union-fields-to-get-distinct-count/57585 "2016-08-09T11:02:56Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![goofansu](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/goofansu/32/4755_2.png) [@goofansu](https://discuss.elastic.co/u/goofansu)\
**Post date:** [August 9, 2016, 11:02am UTC](https://discuss.elastic.co/t/how-to-union-fields-to-get-distinct-count/57585/1 "2016-08-09T11:02:56Z")

</div>

data:

```
[
{"from_uid": 1, "to_uid": 2},
{"from_uid": 2, "to_uid": 1},
{"from_uid": 2, "to_uid": 3}
]

```

I want to query the result like following sql:

```
select count(distinct a.uid) from (
  select from_uid as uid from data group by from_uid
  union
  select to_uid as uid from data group by to_uid
) a

```

I tried cardinality query but it can only query one field.

---

<div class="post-metadata">

**Author:** ![nik9000](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nik9000/32/44947_2.png) [@nik9000](https://discuss.elastic.co/u/nik9000)\
**Post date:** [August 9, 2016, 5:32pm UTC](https://discuss.elastic.co/t/how-to-union-fields-to-get-distinct-count/57585/2 "2016-08-09T17:32:27Z")

</div>

Could you use a [script](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-terms-aggregation.html#search-aggregations-bucket-terms-aggregation-script) and return a list of both values? I haven't tried it but I expect you could do it.

---

<div class="post-metadata">

**Author:** ![jpountz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jpountz/32/45836_2.png) [@jpountz](https://discuss.elastic.co/u/jpountz)\
**Post date:** [August 9, 2016, 9:08pm UTC](https://discuss.elastic.co/t/how-to-union-fields-to-get-distinct-count/57585/3 "2016-08-09T21:08:50Z")

</div>

A script would work, but if you need to run such queries regularly, the best option would be to have a field that contains both values so that aggregations can leverage ordinals (which are only built per field).

---

<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:** [July 5, 2017, 10:29pm UTC](https://discuss.elastic.co/t/how-to-union-fields-to-get-distinct-count/57585/4 "2017-07-05T22:29:00Z")

</div>