# Mutate join filter
**URL:** https://discuss.elastic.co/t/mutate-join-filter/186884
**Category:** Logstash
**Created:** [June 21, 2019, 1:01pm UTC](https://discuss.elastic.co/t/mutate-join-filter/186884 "2019-06-21T13:01:43Z")
**Posts on this page:** 19
**Page:** 1
### Author:  [@Andreasky](https://discuss.elastic.co/u/Andreasky)
#### Post date: [June 21, 2019, 1:01pm UTC](https://discuss.elastic.co/t/mutate-join-filter/186884/1 "2019-06-21T13:01:43Z")
Is it possible to use the mutate join filter to make it possible to make queries across indexes in Kibana? Like joining tables in SQL?
---
### Author:  [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)
#### Post date: [June 23, 2019, 9:09pm UTC](https://discuss.elastic.co/t/mutate-join-filter/186884/2 "2019-06-23T21:09:57Z")
What is the “mutate join filter” you are referring to? What are you looking to achieve?
---
### Author:  [@Andreasky](https://discuss.elastic.co/u/Andreasky)
#### Post date: [June 24, 2019, 6:23am UTC](https://discuss.elastic.co/t/mutate-join-filter/186884/3 "2019-06-24T06:23:49Z")
I'm trying to get information from two different indexes. I've got a log file that contains different messages about a phone call, and I want to get information from all of the messages in e.g. one table in kibana.
---
### Author:  [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)
#### Post date: [June 24, 2019, 7:08am UTC](https://discuss.elastic.co/t/mutate-join-filter/186884/4 "2019-06-24T07:08:14Z")
Can you provide an example of data from the two sources?
---
### Author:  [@Andreasky](https://discuss.elastic.co/u/Andreasky)
#### Post date: [June 24, 2019, 7:37am UTC](https://discuss.elastic.co/t/mutate-join-filter/186884/5 "2019-06-24T07:37:59Z")
"Index" = "message1":
```
"sessionID" = "12345"
"Response" = "answered"
```
"Index" = "message2":
```
"sessionID" = "12345"
"Caller" = 09876543"
"Callee" = 01234567"
```
What I want:
"Index" = "message"
```
"sessionID" = "12345"
"Response" = "answered"
"Caller" = 09876543"
"Callee" = 01234567"
```
---
### Author:  [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)
#### Post date: [June 24, 2019, 8:19am UTC](https://discuss.elastic.co/t/mutate-join-filter/186884/6 "2019-06-24T08:19:06Z")
You will need to join these up before indexing and might be able to get se the translate filter.
---
### Author:  [@Andreasky](https://discuss.elastic.co/u/Andreasky)
#### Post date: [June 24, 2019, 8:20am UTC](https://discuss.elastic.co/t/mutate-join-filter/186884/7 "2019-06-24T08:20:14Z")
Can you give me an example of how to do this?
---
### Author:  [@Badger](https://discuss.elastic.co/u/Badger)
#### Post date: [June 24, 2019, 12:09pm UTC](https://discuss.elastic.co/t/mutate-join-filter/186884/8 "2019-06-24T12:09:24Z")
Another possible approach is doc\_as\_upsert, as mentioned [here](https://discuss.elastic.co/t/aggregate-problem/142572/7).
---
### Author:  [@Andreasky](https://discuss.elastic.co/u/Andreasky)
#### Post date: [June 25, 2019, 8:08am UTC](https://discuss.elastic.co/t/mutate-join-filter/186884/9 "2019-06-25T08:08:32Z")
I tried that, but I don't quite understand how it works, so I didn't manage to make it work. Logstash just shuts down.
```
output {
if [Type] == "adapter" {
file { path => "/some/path/out.txt" codec => plain { format => '{ "update" : {"_id" : "%{sessionID}", "_type" : "doc", "_index" : "someindex"} }
{ "doc": "sessionID": true, "doc_as_upsert" : true }
' } }
}
}
```
---
### Author:  [@Andreasky](https://discuss.elastic.co/u/Andreasky)
#### Post date: [June 25, 2019, 8:29am UTC](https://discuss.elastic.co/t/mutate-join-filter/186884/10 "2019-06-25T08:29:56Z")
So now the configuration file runs, but the out.txt file only looks like this:
```
{"update" : {"_id": "%{sessionid}", "_type" : "doc", "_index" : "someindex"}}
{"doc" : "sessionid": true, "doc_as_upsert" : true}
{"update" : {"_id": "%{sessionid}", "_type" : "doc", "_index" : "someindex"}}
{"doc" : "sessionid": true, "doc_as_upsert" : true}
{"update" : {"_id": "%{sessionid}", "_type" : "doc", "_index" : "someindex"}}
{"doc" : "sessionid": true, "doc_as_upsert" : true}
{"update" : {"_id": "%{sessionid}", "_type" : "doc", "_index" : "someindex"}}
{"doc" : "sessionid": true, "doc_as_upsert" : true}
{"update" : {"_id": "%{sessionid}", "_type" : "doc", "_index" : "someindex"}}
{"doc" : "sessionid": true, "doc_as_upsert" : true}
{"update" : {"_id": "%{sessionid}", "_type" : "doc", "_index" : "someindex"}}
{"doc" : "sessionid": true, "doc_as_upsert" : true}
{"update" : {"_id": "%{sessionid}", "_type" : "doc", "_index" : "someindex"}}
{"doc" : "sessionid": true, "doc_as_upsert" : true}
{"update" : {"_id": "%{sessionid}", "_type" : "doc", "_index" : "someindex"}}
{"doc" : "sessionid": true, "doc_as_upsert" : true}
{"update" : {"_id": "%{sessionid}", "_type" : "doc", "_index" : "someindex"}}
{"doc" : "sessionid": true, "doc_as_upsert" : true}
{"update" : {"_id": "%{sessionid}", "_type" : "doc", "_index" : "someindex"}}
{"doc" : "sessionid": true, "doc_as_upsert" : true}
{"update" : {"_id": "%{sessionid}", "_type" : "doc", "_index" : "someindex"}}
{"doc" : "sessionid": true, "doc_as_upsert" : true}
{"update" : {"_id": "%{sessionid}", "_type" : "doc", "_index" : "someindex"}}
{"doc" : "sessionid": true, "doc_as_upsert" : true}
{"update" : {"_id": "%{sessionid}", "_type" : "doc", "_index" : "someindex"}}
{"doc" : "sessionid": true, "doc_as_upsert" : true}
{"update" : {"_id": "%{sessionid}", "_type" : "doc", "_index" : "someindex"}}
{"doc" : "sessionid": true, "doc_as_upsert" : true}
{"update" : {"_id": "%{sessionid}", "_type" : "doc", "_index" : "someindex"}}
{"doc" : "sessionid": true, "doc_as_upsert" : true}
{"update" : {"_id": "%{sessionid}", "_type" : "doc", "_index" : "someindex"}}
{"doc" : "sessionid": true, "doc_as_upsert" : true}
{"update" : {"_id": "%{sessionid}", "_type" : "doc", "_index" : "someindex"}}
{"doc" : "sessionid": true, "doc_as_upsert" : true}
{"update" : {"_id": "%{sessionid}", "_type" : "doc", "_index" : "someindex"}}
{"doc" : "sessionid": true, "doc_as_upsert" : true}
{"update" : {"_id": "%{sessionid}", "_type" : "doc", "_index" : "someindex"}}
{"doc" : "sessionid": true, "doc_as_upsert" : true}
{"update" : {"_id": "%{sessionid}", "_type" : "doc", "_index" : "someindex"}}
{"doc" : "sessionid": true, "doc_as_upsert" : true}
{"update" : {"_id": "%{sessionid}", "_type" : "doc", "_index" : "someindex"}}
{"doc" : "sessionid": true, "doc_as_upsert" : true}
{"update" : {"_id": "%{sessionid}", "_type" : "doc", "_index" : "someindex"}}
{"doc" : "sessionid": true, "doc_as_upsert" : true}
{"update" : {"_id": "%{sessionid}", "_type" : "doc", "_index" : "someindex"}}
{"doc" : "sessionid": true, "doc_as_upsert" : true}
{"update" : {"_id": "%{sessionid}", "_type" : "doc", "_index" : "someindex"}}
{"doc" : "sessionid": true, "doc_as_upsert" : true}
{"update" : {"_id": "%{sessionid}", "_type" : "doc", "_index" : "someindex"}}
{"doc" : "sessionid": true, "doc_as_upsert" : true}
{"update" : {"_id": "%{sessionid}", "_type" : "doc", "_index" : "someindex"}}
{"doc" : "sessionid": true, "doc_as_upsert" : true}
{"update" : {"_id": "%{sessionid}", "_type" : "doc", "_index" : "someindex"}}
{"doc" : "sessionid": true, "doc_as_upsert" : true}
{"update" : {"_id": "%{sessionid}", "_type" : "doc", "_index" : "someindex"}}
{"doc" : "sessionid": true, "doc_as_upsert" : true}
{"update" : {"_id": "%{sessionid}", "_type" : "doc", "_index" : "someindex"}}
{"doc" : "sessionid": true, "doc_as_upsert" : true}
{"update" : {"_id": "%{sessionid}", "_type" : "doc", "_index" : "someindex"}}
{"doc" : "sessionid": true, "doc_as_upsert" : true}
{"update" : {"_id": "%{sessionid}", "_type" : "doc", "_index" : "someindex"}}
{"doc" : "sessionid": true, "doc_as_upsert" : true}
{"update" : {"_id": "%{sessionid}", "_type" : "doc", "_index" : "someindex"}}
{"doc" : "sessionid": true, "doc_as_upsert" : true}
{"update" : {"_id": "%{sessionid}", "_type" : "doc", "_index" : "someindex"}}
{"doc" : "sessionid": true, "doc_as_upsert" : true}
```
This was not exactly the result I was hoping for
---
### Author:  [@Badger](https://discuss.elastic.co/u/Badger)
#### Post date: [June 25, 2019, 3:22pm UTC](https://discuss.elastic.co/t/mutate-join-filter/186884/11 "2019-06-25T15:22:28Z")
Let me expand upon that approach. Suppose we have two sources of data. Firstly we have a file that contains a session identifier and a response
```
{ "sessionID": "12345", "Response": "answered" }
{ "sessionID": "23456", "Response": "dropped" }
```
Then we have another file that contains the caller and callee
```
{ "sessionID": "12345", "Caller": "09876543", "Callee": "01234567" }
{ "sessionID": "23456", "Caller": "09871234", "Callee": "76543210" }
```
We can use file inputs to read these and tag them, so that we know which events have which format
```
input {
file { path => "/home/foo.txt" tags => ["foo"] sincedb_path => "/dev/null" start_position => beginning }
file { path => "/home/bar.txt" tags => ["bar"] sincedb_path => "/dev/null" start_position => beginning }
}
```
We use a json filter to parse them
```
filter { json { source => "message" } }
```
In the output section we write out the appropriate fields for each type
```
output {
if "foo" in [tags] {
file { path => "/tmp/out.txt" codec => plain { format => '{ "update" : {"_id" : "%{sessionID}", "_type" : "doc", "_index" : "someindex"} }
{ "doc": { "Response": "%{Response}" }, "doc_as_upsert" : true }
' } }
}
if "bar" in [tags] {
file { path => "/tmp/out.txt" codec => plain { format => '{ "update" : {"_id" : "%{sessionID}", "_type" : "doc", "_index" : "someindex"} }
{ "doc": { "Caller": "%{Caller}", "Callee": "%{Callee}" }, "doc_as_upsert" : true }
' } }
}
}
```
This results in the following output
```auto
{ "update" : {"_id" : "12345", "_type" : "doc", "_index" : "someindex"} }
{ "doc": { "Response": "answered" }, "doc_as_upsert" : true }
{ "update" : {"_id" : "23456", "_type" : "doc", "_index" : "someindex"} }
{ "doc": { "Response": "dropped" }, "doc_as_upsert" : true }
{ "update" : {"_id" : "12345", "_type" : "doc", "_index" : "someindex"} }
{ "doc": { "Caller": "09876543", "Callee": "01234567" }, "doc_as_upsert" : true }
{ "update" : {"_id" : "23456", "_type" : "doc", "_index" : "someindex"} }
{ "doc": { "Caller": "09871234", "Callee": "76543210" }, "doc_as_upsert" : true }
```
If you load that into elasticsearch using curl then the first update with a given \_id will insert the document with the associated fields, and subsequent udpates will update the document with additional fields.
---
### Author:  [@Andreasky](https://discuss.elastic.co/u/Andreasky)
#### Post date: [June 26, 2019, 5:43am UTC](https://discuss.elastic.co/t/mutate-join-filter/186884/12 "2019-06-26T05:43:43Z")
This is probably a stupid question, but how do I do this when all of the information is in the same file? I have used several grok filters to extract the infromation that I need from the lines in the file. Do I need to save them in different files first?
Thank you so much for your help!
---
### Author:  [@Badger](https://discuss.elastic.co/u/Badger)
#### Post date: [June 26, 2019, 12:58pm UTC](https://discuss.elastic.co/t/mutate-join-filter/186884/13 "2019-06-26T12:58:49Z")
You can use any conditional that tells you whether the event contains [Response] or [Callee]. It might be as simple as
```
output {
if [Response] {
file { path => "/tmp/out.txt" codec => plain { format => '{ "update" : {"_id" : "% {sessionID}", "_type" : "doc", "_index" : "someindex"} }
{ "doc": { "Response": "%{Response}" }, "doc_as_upsert" : true }
' } }
}
if [Callee] {
file { path => "/tmp/out.txt" codec => plain { format => '{ "update" : {"_id" : "%{sessionID}", "_type" : "doc", "_index" : "someindex"} }
{ "doc": { "Caller": "%{Caller}", "Callee": "%{Callee}" }, "doc_as_upsert" : true }
' } }
}
}
```
---
### Author:  [@Andreasky](https://discuss.elastic.co/u/Andreasky)
#### Post date: [June 27, 2019, 8:43am UTC](https://discuss.elastic.co/t/mutate-join-filter/186884/14 "2019-06-27T08:43:51Z")
Now my problem is that this command:
```
curl -XPOST 'http://localhost:9200/someindex/doc/_bulk' -H "Content-Type: application/json" --data-binary @/home/summer19/dipper_prod_logs/out.txt
```
won't run unless i exchange "\_id" with "id", "\_type" with "type" and "\_index" with "index". But when I do that, nothig happens to the docs in the "someindex"-index.
Any suggestions to how i can fix this?
---
### Author:  [@Badger](https://discuss.elastic.co/u/Badger)
#### Post date: [June 27, 2019, 1:52pm UTC](https://discuss.elastic.co/t/mutate-join-filter/186884/15 "2019-06-27T13:52:16Z")
You can probably get rid of type/\_type altogether, but the [current documentation](https://www.elastic.co/guide/en/elasticsearch/reference/current/docs-bulk.html) says you should be using \_index and \_id. What error do you get?
---
### Author:  [@Andreasky](https://discuss.elastic.co/u/Andreasky)
#### Post date: [June 28, 2019, 8:19am UTC](https://discuss.elastic.co/t/mutate-join-filter/186884/16 "2019-06-28T08:19:32Z")
```
{"error":{"root_cause":[{"type":"x_content_parse_exception","reason":"[1:852] [UpdateRequest] failed to parse field [doc]"}],"type":"x_content_parse_exception","reason":"[1:852] [UpdateRequest] failed to parse field [doc]","caused_by":{"type":"json_parse_exception","reason":"Unexpected character ('_' (code 95)): was expecting comma to separate Object entries\n at ...
```
This is the error message I get.
---
### Author:  [@Badger](https://discuss.elastic.co/u/Badger)
#### Post date: [June 28, 2019, 1:50pm UTC](https://discuss.elastic.co/t/mutate-join-filter/186884/17 "2019-06-28T13:50:32Z")
That suggests the \_id is not inside double quotes. Can you post the first 2 lines of what you are posting using curl?
---
### Author:  [@Andreasky](https://discuss.elastic.co/u/Andreasky)
#### Post date: [July 5, 2019, 9:22am UTC](https://discuss.elastic.co/t/mutate-join-filter/186884/18 "2019-07-05T09:22:06Z")
Thank you for the help! I tried using the aggregate filter plugin instead. Not working perfectly yet, but I managed to get all the information from one sessionid in the same document.
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [August 2, 2019, 9:22am UTC](https://discuss.elastic.co/t/mutate-join-filter/186884/19 "2019-08-02T09:22:10Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.