# How do I combine fields from 2 different docs (and indices) within Kibana?

**URL:** <https://discuss.elastic.co/t/how-do-i-combine-fields-from-2-different-docs-and-indices-within-kibana/277819>\
**Category:** Kibana\
**Tags:** transforms\
**Created:** [July 5, 2021, 11:18am UTC](https://discuss.elastic.co/t/how-do-i-combine-fields-from-2-different-docs-and-indices-within-kibana/277819 "2021-07-05T11:18:27Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![Luukv](https://avatars.discourse-cdn.com/v4/letter/l/ee59a6/32.png) [@Luukv](https://discuss.elastic.co/u/Luukv)\
**Post date:** [July 5, 2021, 11:18am UTC](https://discuss.elastic.co/t/how-do-i-combine-fields-from-2-different-docs-and-indices-within-kibana/277819/1 "2021-07-05T11:18:28Z")

</div>

Hello,

We are trying to get an index with docs combining fields from 2 different (already existing) indices. We tried using the transform, but got an index with both docs, still separate however. We are not sure if we used this transform function the right way, so this might still be a possibility. [imgur image of our transform attempt](https://imgur.com/xhmtolD)

I already tried creating an index pattern which includes both the indices I need the fields from. In visualizations the fields I need are appearing, but I can't filter data from fields from index a on a fields from index b (within this same index pattern). It will show no data when I try this.

Are there some things we need to change in our transform, or are there different ways to achieve the aforementioned goal (an index with docs which have fields from the 2 already exisiting indices we have)?

We are on Kibana v7.8.0. If any other information is needed, let me know!  
Kind regards.

---

<div class="post-metadata">

**Author:** ![mangeshmj1992](https://avatars.discourse-cdn.com/v4/letter/m/d9b06d/32.png) [@mangeshmj1992](https://discuss.elastic.co/u/mangeshmj1992)\
**Post date:** [July 5, 2021, 11:54am UTC](https://discuss.elastic.co/t/how-do-i-combine-fields-from-2-different-docs-and-indices-within-kibana/277819/2 "2021-07-05T11:54:35Z")

</div>

Hi @Luukv ,  
Can you please share sample data from both indexes and mention fields which you need combine in one log

---

<div class="post-metadata">

**Author:** ![Luukv](https://avatars.discourse-cdn.com/v4/letter/l/ee59a6/32.png) [@Luukv](https://discuss.elastic.co/u/Luukv)\
**Post date:** [July 6, 2021, 1:10pm UTC](https://discuss.elastic.co/t/how-do-i-combine-fields-from-2-different-docs-and-indices-within-kibana/277819/3 "2021-07-06T13:10:30Z")

</div>

Hello, thanks for you response. See below for the samples.

> _ **Index 1, Clicks:** _  
> \_id  
> id number
> 
> \_index  
> company\_click\_out
> 
> \_score  
> 0
> 
> \_type  
> \_doc
> 
> **click\_box\_title**  
> **specific category**
> 
> click\_out\_id  
> click id number
> 
> **click\_price**  
> **1.00**
> 
> clickbox\_category\_id  
> 366
> 
> company\_id  
> 1111
> 
> **company\_location\_city**  
> **Utrecht**
> 
> company\_location\_city\_keyword  
> Company A Utrecht
> 
> company\_location\_country  
> ENG
> 
> company\_location\_geo\_point  
> 14.422657, 17.94846
> 
> company\_location\_id  
> 85 642
> 
> **company\_location\_name**  
> **Franchise A**
> 
> company\_location\_postcode  
> 43253
> 
> **company\_name**  
> **Company A**
> 
> **created\_at**  
> **Timefield when the doc is added**
> 
> **day\_of\_week\_keyword**  
> **monday**
> 
> * * *
> 
> **hour\_of\_day**  
> **1**
> 
> **is\_free\_click\_int**  
> **0**
> 
> * * *
> 
> **is\_paid\_click\_int**  
> **1**
> 
> **missed\_revenue**  
> **0,00**
> 
> **monetization\_rate**  
> **100%**
> 
> **month\_keyword**  
> **january**
> 
> number\_of\_clicks  
> 1
> 
> occurrences\_times
> 
> **original\_price**  
> **1.00**
> 
> **paid\_clicks\_percentage**  
> **100%**
> 
> **parent\_category\_title**  
> **Parent category**
> 
> type  
> booking
> 
> updated\_at  
> Jan 1, 2020, @ 00:00:00
> 
> visit\_id  
> id of the session
> 
> visitor\_ip\_address  
> ip address of the user
> 
> visitor\_ip\_address\_string  
> ip address of the user in string type
> 
> visitor\_user\_agent  
> device information of the user
> 
> **week\_number**  
> **1**

* * *

-- Above is index a, Clicks, below this line starts index b, conversions.

> _ **Index 2, conversion:** _
> 
> * * *
> 
> \_id  
> 123
> 
> \_index  
> company\_conversion
> 
> \_score  
> 0
> 
> \_type  
> \_doc
> 
> clickbox\_category\_id  
> 123
> 
> **clickbox\_category\_name**  
> **Category**
> 
> conversion\_id  
> 123
> 
> **created\_at**  
> **Jun 4, 2021 @ 14:00:00.000**
> 
> **experiments**  
> **{**  
> **String with some experimental data**  
> **}**
> 
> **offer\_date\_from**  
> **Start date of the offer**
> 
> **offer\_date\_until**  
> **End date of the offer**
> 
> permanent\_visitor\_id  
> 27346278\_a424b
> 
> rental\_item\_id  
> 1435
> 
> **rental\_item\_name**  
> **specific item name**
> 
> rental\_service\_provider\_id  
> 23452
> 
> rental\_service\_provider\_location\_id  
> 23552
> 
> **rental\_service\_provider\_location\_name**  
> **Company A Utrecht**
> 
> **rental\_service\_provider\_name**  
> **Franchise A - Company A Utrecht**
> 
> **type**  
> **booking**
> 
> updated\_at  
> Jun 4, 2021 @ 14:00:00.000
> 
> **visit\_id**  
> **id of the session**

The bold text is the field with some sample data which I would like in the combined doc. These are from the 2 indices, clicks and conversions, both have the field 'visit\_id', which might add some possibilities when it comes to combining docs.

Hope this information is an answer to your request and you can help us out!

---

<div class="post-metadata">

**Author:** ![Luukv](https://avatars.discourse-cdn.com/v4/letter/l/ee59a6/32.png) [@Luukv](https://discuss.elastic.co/u/Luukv)\
**Post date:** [July 8, 2021, 5:28pm UTC](https://discuss.elastic.co/t/how-do-i-combine-fields-from-2-different-docs-and-indices-within-kibana/277819/4 "2021-07-08T17:28:06Z")

</div>

If someone were to come across this post unsure if it's still relevant/active, please do share your thoughts, tips or maybe some sources which might be relevant I could check out. This issue has not been resolved and is holding me back from setting up the dashboards we need, so any help is appreciated!

---

<div class="post-metadata">

**Author:** ![Hendrik\_Muhs](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hendrik_muhs/32/25802_2.png) [@Hendrik\_Muhs](https://discuss.elastic.co/u/Hendrik_Muhs)\
**Post date:** [July 12, 2021, 6:17am UTC](https://discuss.elastic.co/t/how-do-i-combine-fields-from-2-different-docs-and-indices-within-kibana/277819/5 "2021-07-12T06:17:03Z")

</div>

> [@Luukv](#):
>
> We tried using the transform, but got an index with both docs, still separate however.

Are you getting 2 _documents_ per `visit_id`?

This is means you have a mapping problem for index `a` and `b`. Check the index mappings for both. From the screenshot it seems you have a multi-field that maps `visit_id.keyword` to `keyword`. Does it look the same for both?

Or: Are you getting the 1 document but from the `scripted metric` you are using, you get a list of output documents:

```auto
    "conversions": [
    {
        # doc from index a
    },
    {
        # doc from index b
    }
    ]

```

This is a problem of your used script, you are _collapsing_ the docs in a list.

A generic way of a _join_ with a `scripted_metric`:

```auto
        "scripted_metric": {
          "init_script": "state.join = new HashMap()",
          "map_script": "String[] fields = new String[] {'click_box_title', 'click_price', 'clickbox_category_name'}; for (e in fields) { if (doc.containsKey(e) && doc[e].size() > 0) {state.join.put(e, doc[e])}}",
          "combine_script": "return state.join",
          "reduce_script": "String[] fields = new String[] {'click_box_title', 'click_price', 'clickbox_category_name'}; Map j=new HashMap(); for (s in states) {for (e in fields) { if (s.containsKey(e)) {j.put(e, s[e].get(0))}}} return j;"
        }

```

I only took 3 of your fields as an example, please add the remaining field names yourself.

Alternatively you can use aggregations like `min`, `max` for numeric or date fields like this:

```auto
"offer_date_from": {
    "min": {
        "field": "offer_date_from"
    }
}

```

For keyword fields there is unfortunately no easy way at the moment (support for `top_metrics` - available in a future release - will solve this gap). That means for `keyword` you have to use `scripted_metric`.

For fields that are available and have the _same_ value in _both_ indices like `type` you can put it into the `group_by` section.

---

<div class="post-metadata">

**Author:** ![Luukv](https://avatars.discourse-cdn.com/v4/letter/l/ee59a6/32.png) [@Luukv](https://discuss.elastic.co/u/Luukv)\
**Post date:** [July 12, 2021, 9:38am UTC](https://discuss.elastic.co/t/how-do-i-combine-fields-from-2-different-docs-and-indices-within-kibana/277819/6 "2021-07-12T09:38:31Z")

</div>

Hello, thanks for your response! I'm not sure if I am misunderstanding what you're saying or that maybe we're not completely on the same page. I'll try to explain the situation a bit more clear, as I see my initial post is a bit messy, apologies for that.

We have 2 indices, clicks and conversions. Both of these indices contain the visit\_id field. In our case not all clicks turn into conversions, therefore the clicks index will have more docs, and thus visit\_id records. For the clicks that **do** turn into a conversion, a doc is created in the conversions index, with the visit\_id of said conversion.

There is no data regarding clicks in the conversion index, and no data regarding conversions in the click index. The clicks index creates docs with fields as listed above, and the same goes for the conversions index respectively. So both these indices have 1 doc structure **each** , I listed this structure for both indices above (the sample data).

What we are looking for is a 3rd index with a new doc structure, which has fields from both the clicks index as the conversions index. In the sample data above, there are some fields of whicht the text I made bold, these are the fields that we want in the new, 3rd index.

If your post is a solution to the request I described above, I am misunderstanding you. In this case, could you please elaborate what a 'scripted\_metric' exactly is, and what the output of the script you shared in your response would be?

---

<div class="post-metadata">

**Author:** ![Hendrik\_Muhs](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hendrik_muhs/32/25802_2.png) [@Hendrik\_Muhs](https://discuss.elastic.co/u/Hendrik_Muhs)\
**Post date:** [July 12, 2021, 10:52am UTC](https://discuss.elastic.co/t/how-do-i-combine-fields-from-2-different-docs-and-indices-within-kibana/277819/7 "2021-07-12T10:52:12Z")

</div>

The solution I provided is based on your image from the 1st post: [Imgur: The magic of the Internet](https://imgur.com/xhmtolD)

Maybe you can share your config as text, than I can reuse it and mark the parts.

There you group on `visit_id` and you use a [`scripted_metric`](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-scripted-metric-aggregation.html) aggregation to _collapse_ fields. The script concatenates the docs from both indices in a list, see my example. Maybe you can post the output of the `_preview` command to confirm that.

> [@Luukv](#):
>
> What we are looking for is a 3rd index with a new doc structure, which has fields from both the clicks index as the conversions index. In the sample data above, there are some fields of whicht the text I made bold, these are the fields that we want in the new, 3rd index.

That's why I think you want 1 structure with 1 value for each field, either from index `a` or `b`. The script I provided is a replacement for the `scripted metric` of the transform in the image. It takes the 1st occurrence of a value of a field and puts it into the output structure.

A possible output:

```auto
    "unique_id": "a", 
    "conversions": {
        "click_box_title": "title",
        "click_price": 22,
        ...
    }

```

In case a field is not found (no conversion for a click), the field isn't created.

I repeat what I said about numeric/date fields, you can use a `min` or `max` aggregation instead of a `scripted_metric` for those fields.

```auto
POST _transform/_preview
{
"source": {...},
"pivot": {
    "group_by": {
        "unique-id": {...}
    },
    "aggregations": {
        "offer_date_from": {
            "min": {
                "field": "offer_date_from"
            }
        },
        "original_price": {
            "min": {
                "field": "original_price"
            }
        }
    }
}

```

should return 1 document with 2 _joined_ fields from `a` and `b` and the field `unique-id`.

Only for non-numeric fields you need the `scripted_metric`.

---

<div class="post-metadata">

**Author:** ![Luukv](https://avatars.discourse-cdn.com/v4/letter/l/ee59a6/32.png) [@Luukv](https://discuss.elastic.co/u/Luukv)\
**Post date:** [July 14, 2021, 12:48pm UTC](https://discuss.elastic.co/t/how-do-i-combine-fields-from-2-different-docs-and-indices-within-kibana/277819/8 "2021-07-14T12:48:03Z")

</div>

@Hendrik_Muhs Thanks for the response, I would like to discuss what you mentioned with a colleague who is unavailable at the moment. I'll get back to you asap.

---

<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:** [August 11, 2021, 12:48pm UTC](https://discuss.elastic.co/t/how-do-i-combine-fields-from-2-different-docs-and-indices-within-kibana/277819/9 "2021-08-11T12:48:13Z")

</div>

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