# How to Display Results in Table that are "Intersection" of two Queries?

**URL:** <https://discuss.elastic.co/t/how-to-display-results-in-table-that-are-intersection-of-two-queries/360978>\
**Category:** Kibana\
**Tags:** vega, transforms, visualisation\
**Created:** [June 6, 2024, 2:45pm UTC](https://discuss.elastic.co/t/how-to-display-results-in-table-that-are-intersection-of-two-queries/360978 "2024-06-06T14:45:41Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![bianca\_s](https://avatars.discourse-cdn.com/v4/letter/b/ba9def/32.png) [@bianca\_s](https://discuss.elastic.co/u/bianca_s)\
**Post date:** [June 6, 2024, 2:45pm UTC](https://discuss.elastic.co/t/how-to-display-results-in-table-that-are-intersection-of-two-queries/360978/1 "2024-06-06T14:45:41Z")

</div>

Hi all,

we have the following use case:

We have an index containing logs of authentications from different devices. The data looks something like this:

| device\_id | region\_id | district\_id |
| --- | --- | --- |
| 1234 | A | district-1 |
| 5678 | A | district-3 |
| 1234 | B | district-5 |
| 9876 | B | district-2 |
| 9876 | A | district-5 |
| 1234 | B | district-6 |
| 9876 | C | district-8 |

Now we want to display the devices that have authenticated in BOTH regions "A" and "B" and also see the respective districts on a Kibana Dashboard.

Basically something like this:

| device\_id | region\_id | disctrict\_ids |
| --- | --- | --- |
| 1234 | A | district-1 |
| 1234 | B | district-5, district-6 |
| 9876 | A | district-5 |
| 9876 | B | district-2 |

Alternatively, it might also be sufficient for us to filter for all logs about devices that have authenticated in BOTH regions "A" and "B", like this:

| device\_id | region\_id | district\_id |
| --- | --- | --- |
| 1234 | A | district-1 |
| 1234 | B | district-5 |
| 1234 | B | district-6 |
| 9876 | B | district-2 |
| 9876 | A | district-5 |

We haven't found any solution to this yet. We also thought about using vega or transforms but did not really get ahead with this.

Thank you!

---

<div class="post-metadata">

**Author:** ![bianca\_s](https://avatars.discourse-cdn.com/v4/letter/b/ba9def/32.png) [@bianca\_s](https://discuss.elastic.co/u/bianca_s)\
**Post date:** [June 19, 2024, 12:39pm UTC](https://discuss.elastic.co/t/how-to-display-results-in-table-that-are-intersection-of-two-queries/360978/2 "2024-06-19T12:39:18Z")

</div>

Anyone any idea? 🙂 If I can provide any more information, please let me know.

---

<div class="post-metadata">

**Author:** ![Marco\_Liberati](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/marco_liberati/32/82953_2.png) [@Marco\_Liberati](https://discuss.elastic.co/u/Marco_Liberati)\
**Post date:** [June 20, 2024, 12:20pm UTC](https://discuss.elastic.co/t/how-to-display-results-in-table-that-are-intersection-of-two-queries/360978/4 "2024-06-20T12:20:46Z")

</div>

Are you interested in the `device_id`s who authenticated to multiple regions or also the `district_id` they authenticated into?

Because as for the former I think you can build a table in Lens using the `Collapse by` feature over a `region_id` dimension to have something like:

```auto
device_id | count_over_regions
1234 | 2
9876 | 2
5678 | 1

```

I think with Vega it would be possible to build something more specific, but it take way more effort. Knowing exactly what you need would help to share the answer here.

---

<div class="post-metadata">

**Author:** ![bianca\_s](https://avatars.discourse-cdn.com/v4/letter/b/ba9def/32.png) [@bianca\_s](https://discuss.elastic.co/u/bianca_s)\
**Post date:** [June 27, 2024, 11:56am UTC](https://discuss.elastic.co/t/how-to-display-results-in-table-that-are-intersection-of-two-queries/360978/5 "2024-06-27T11:56:55Z")

</div>

Thank you for your reply, @Marco_Liberati !

We are interested in both the `device_id` s who authenticated to multiple regions AND the `district_id` they authenticated into. I have included two tables how that could look like in my initial question. Option A) is one row per `device_id` and `region_id` and showing all `district_id` s as an array. Option B) is basically one row per `device_id`, `region_id` and `district_id`.

Regarding collapse by:

This feature does not seem to be available yet in 7.16 which is the version we're currently using - sorry for not mentioning the version in my initial question.

We're planning to migrate to 8.x in the near future but I am not sure if collapse by will work anyways for our use case (e.g. it only seems to work for numeric fields). But I will keep this in the back of my head.

Regarding Vega: Can you give me a hint how that could be implemented in Vega? I have read that Vega does not support tables and am not sure how to display the required information in any other format.