# Join query (maps)

**URL:** <https://discuss.elastic.co/t/join-query-maps/216657>\
**Category:** Kibana\
**Tags:** maps\
**Created:** [January 27, 2020, 1:15pm UTC](https://discuss.elastic.co/t/join-query-maps/216657 "2020-01-27T13:15:06Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Pieter\_Agenbag](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pieter_agenbag/32/4562_2.png) [@Pieter\_Agenbag](https://discuss.elastic.co/u/Pieter_Agenbag)\
**Post date:** [January 27, 2020, 1:15pm UTC](https://discuss.elastic.co/t/join-query-maps/216657/1 "2020-01-27T13:15:06Z")

</div>

Hi , I was playing around in the maps section of Kibana , which I havent used before ... and stumbled on the "join" section in the Documents layer. Although I have no specific use for it in terms of maps ... it piqued my interest for other applications.

Upon inspecting the developer console ... I found it submitting the following query

```
{
  "size": 0,
  "aggs": {
    "join": {
      "terms": {
        "field": "ciClassKey",
        "order": {
          "_count": "desc"
        },
        "size": 10000
      },
      "aggs": {
        " __kbnjoin__ max_of_unitCount_groupby_metadata.*.ciClassKey": {
          "max": {
            "field": "unitCount"
          }
        }
      }
    }
  },
  "_source": {
    "excludes": []
  },
  "stored_fields": [
    "*"
  ],
  "script_fields": {},
  "docvalue_fields": [
    {
      "field": "lastdate",
      "format": "date_time"
    }
  ],
  "query": {
    "bool": {
      "must": [],
      "filter": [
        {
          "match_all": {}
        }
      ],
      "should": [],
      "must_not": []
    }
  }
}

```

I couldnt actually get it to return any rows ...but was wondering if someone had some insight into the join aspect of it. Is it something we can use in normal ES queries ...or does Kibana pre-parse the query to do a separate second query to join the results from the first one on ?

---

<div class="post-metadata">

**Author:** ![jsanz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jsanz/32/53734_2.png) [@jsanz](https://discuss.elastic.co/u/jsanz)\
**Post date:** [January 28, 2020, 2:54pm UTC](https://discuss.elastic.co/t/join-query-maps/216657/2 "2020-01-28T14:54:17Z")

</div>

just to clarify, you mean you could get results from the maps application in a layer, but running the query manually you didn't get any results?

---

<div class="post-metadata">

**Author:** ![Nathan\_Reese](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nathan_reese/32/84829_2.png) [@Nathan\_Reese](https://discuss.elastic.co/u/Nathan_Reese)\
**Post date:** [January 28, 2020, 3:03pm UTC](https://discuss.elastic.co/t/join-query-maps/216657/3 "2020-01-28T15:03:55Z")

</div>

> but was wondering if someone had some insight into the join aspect of it.

Here is some documentation detailing how terms joins work, [Term join | Kibana Guide [8.11] | Elastic](https://www.elastic.co/guide/en/kibana/current/terms-join.html#_how_a_term_join_works)

> Is it something we can use in normal ES queries ...or does Kibana pre-parse the query to do a separate second query to join the results from the first one on ?

The request is just a standard \_search terms aggregation request. The results from the terms aggregation are joined with the documents for the layer in the client with logic from Kibana.

---

<div class="post-metadata">

**Author:** ![Pieter\_Agenbag](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pieter_agenbag/32/4562_2.png) [@Pieter\_Agenbag](https://discuss.elastic.co/u/Pieter_Agenbag)\
**Post date:** [January 30, 2020, 1:07pm UTC](https://discuss.elastic.co/t/join-query-maps/216657/4 "2020-01-30T13:07:55Z")

</div>

> The request is just a standard \_search terms aggregation request. The results from the terms aggregation are joined with the documents for the layer in the client with logic from Kibana.

Thats what I initially thought .. but how does ES handle

> "aggs": {  
> "\_\_kbnjoin\_\_max\_of\_unitCount\_groupby\_metadata.\*.ciClassKey": {  
> "max": {  
> "field": "unitCount"  
> }  
> }  
> }

Thats the field in the \>other\< index. But I suppose then Kibana parses out the **kbnjoin** \* aggs and creates a second request based on it ... and then joins the results after the fact.

Would be awesome if we could have that kind of functionality IN elasticsearch . I understand scaling concerns .... but would have though it could me mitigated with a "too\_many\_buckets\_exception" type breaker.

---

<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:** [February 27, 2020, 1:08pm UTC](https://discuss.elastic.co/t/join-query-maps/216657/5 "2020-02-27T13:08:04Z")

</div>

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