# Combining multiple documents based on ID **URL:** \ **Category:** Logstash\ **Created:** [October 27, 2017, 7:57am UTC](https://discuss.elastic.co/t/combining-multiple-documents-based-on-id/105534 "2017-10-27T07:57:22Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![qube](https://avatars.discourse-cdn.com/v4/letter/q/ea666f/32.png) [@qube](https://discuss.elastic.co/u/qube)\ **Post date:** [October 27, 2017, 7:57am UTC](https://discuss.elastic.co/t/combining-multiple-documents-based-on-id/105534/1 "2017-10-27T07:57:22Z")
Hello logstash! I'm new to Elastic stack. We are transferring data from Sql Server to ElasticSearch using LogStash's jdbc plugin. So far, it's fine. Normally jdbc plugin creates a new document for each Sql Server row, but I want to create a new document by combining these multiple documents based on ID column. How can I do that? Our data like: ``` id column1 column2 1 A xyz 2 B xxx 3 C yyy 4 D xyz 5 E xyz ``` I want to combine 1, 4, and 5 records in a new document based on column2, xyz. Records must be added to the new document according to the date order in Sql Server. I can do this using order by, but I need help in creating a new document. We can create a new document from records already on ElasticSearch, or we can create a new document based on column2 when the records pass through LogStash, so there won't be extra record on ElasticSearch. Thanks for your interest. ---
**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\ **Post date:** [October 29, 2017, 8:44am UTC](https://discuss.elastic.co/t/combining-multiple-documents-based-on-id/105534/2 "2017-10-29T08:44:44Z")
So you want an array with all the `id` or `column1` name? ---
**Author:** ![qube](https://avatars.discourse-cdn.com/v4/letter/q/ea666f/32.png) [@qube](https://discuss.elastic.co/u/qube)\ **Post date:** [October 29, 2017, 4:18pm UTC](https://discuss.elastic.co/t/combining-multiple-documents-based-on-id/105534/3 "2017-10-29T16:18:06Z")
Hello Mark, thank you for your answer! LogStash's jdbc plugin creates a json object for each row. This is fine. ``` { id: 1 column1: A column2: xyz } ``` What I need is to have a single json object by grouping all the rows according to the column2. ``` { id: 1 column1: A column2: xyz id: 4 column1: D column2: xyz id: 5 column1: E column2: xyz } ``` Can Logstash do that, grouping objects, for column2=xyz in the example, before sending to Elasticsearch? How? ---
**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\ **Post date:** [October 29, 2017, 7:55pm UTC](https://discuss.elastic.co/t/combining-multiple-documents-based-on-id/105534/4 "2017-10-29T19:55:13Z")
That second one won't be valid as you have multiples of the same field name. You should be able to do a [merge](https://www.elastic.co/guide/en/logstash/current/plugins-filters-mutate.html#plugins-filters-mutate-merge) so you end up with; ```auto { id: [1, 4, 5] column1: [A, D, E] column2: xyz } ``` ---
**Author:** ![qube](https://avatars.discourse-cdn.com/v4/letter/q/ea666f/32.png) [@qube](https://discuss.elastic.co/u/qube)\ **Post date:** [October 29, 2017, 8:51pm UTC](https://discuss.elastic.co/t/combining-multiple-documents-based-on-id/105534/5 "2017-10-29T20:51:40Z")
I think the id column is confusing. It does not matter what the id is. id may be anything unique from 1, 4, and 5. Does column2 mean that it can not be combined into a single document because it is a repeating column? Is not it possible with LogStash to get the following document? ``` { _id: unique_value (I can hold this new document with Elasticsearch's _id column) column1: A column2: xyz id: 4 column1: D column2: xyz id: 5 column1: E column2: xyz } ``` elasticsearch filter plugin can help, or aggregate plugin? I read about them, but I need some help how to do.. Thanks! ---
**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\ **Post date:** [October 29, 2017, 9:05pm UTC](https://discuss.elastic.co/t/combining-multiple-documents-based-on-id/105534/6 "2017-10-29T21:05:29Z")
Thing is that it will still collapse those multiple `id`, `column1` and `column2` fields into arrays. So you will never get that exact format. ---
**Author:** ![qube](https://avatars.discourse-cdn.com/v4/letter/q/ea666f/32.png) [@qube](https://discuss.elastic.co/u/qube)\ **Post date:** [October 30, 2017, 7:39am UTC](https://discuss.elastic.co/t/combining-multiple-documents-based-on-id/105534/7 "2017-10-30T07:39:13Z")
Hello Mark, What about this? Adding documents in succession. Still not possible with Logstash? ``` { { id: 1 column1: A column2: xyz }, { id: 4 column1: D column2: xyz }, { id: 5 column1: E column2: xyz } } ``` ---
**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\ **Post date:** [October 30, 2017, 10:07am UTC](https://discuss.elastic.co/t/combining-multiple-documents-based-on-id/105534/8 "2017-10-30T10:07:20Z")
That is possible, yes. ---
**Author:** ![qube](https://avatars.discourse-cdn.com/v4/letter/q/ea666f/32.png) [@qube](https://discuss.elastic.co/u/qube)\ **Post date:** [October 30, 2017, 10:37am UTC](https://discuss.elastic.co/t/combining-multiple-documents-based-on-id/105534/9 "2017-10-30T10:37:14Z")
OK 🙂 So, how can I achieve this? Plugin? Script? Can you please guide me on this matter? ---
**Author:** ![qube](https://avatars.discourse-cdn.com/v4/letter/q/ea666f/32.png) [@qube](https://discuss.elastic.co/u/qube)\ **Post date:** [October 31, 2017, 6:17am UTC](https://discuss.elastic.co/t/combining-multiple-documents-based-on-id/105534/10 "2017-10-31T06:17:04Z")
This could be done with a Logstash plugin? I'm trying with some examples but I have not succeeded yet. Can someone please give me a start? Thank you ~ ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [October 31, 2017, 6:54am UTC](https://discuss.elastic.co/t/combining-multiple-documents-based-on-id/105534/11 "2017-10-31T06:54:26Z")
Your latest example is still not valid JSON. How are you going to use/query this data? ---
**Author:** ![qube](https://avatars.discourse-cdn.com/v4/letter/q/ea666f/32.png) [@qube](https://discuss.elastic.co/u/qube)\ **Post date:** [October 31, 2017, 8:40am UTC](https://discuss.elastic.co/t/combining-multiple-documents-based-on-id/105534/12 "2017-10-31T08:40:30Z")
Hello Christian, I'm not sure how json format should be, but I'm expecting to query with grouped column, column2: xyz in the example. So, my problem is here certain, to get all the documents into a new one big document according to the column2 value. How can I do that Christian, with correct json format? ---
**Author:** ![qube](https://avatars.discourse-cdn.com/v4/letter/q/ea666f/32.png) [@qube](https://discuss.elastic.co/u/qube)\ **Post date:** [November 1, 2017, 5:13am UTC](https://discuss.elastic.co/t/combining-multiple-documents-based-on-id/105534/13 "2017-11-01T05:13:36Z")
Any ideas? ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [November 1, 2017, 7:42am UTC](https://discuss.elastic.co/t/combining-multiple-documents-based-on-id/105534/14 "2017-11-01T07:42:47Z")
Are you going to query the data using one of the language clients or use Kibana? What does this data really represent? ---
**Author:** ![qube](https://avatars.discourse-cdn.com/v4/letter/q/ea666f/32.png) [@qube](https://discuss.elastic.co/u/qube)\ **Post date:** [November 1, 2017, 8:46am UTC](https://discuss.elastic.co/t/combining-multiple-documents-based-on-id/105534/15 "2017-11-01T08:46:55Z")
Christian, I'm going to use Kibana, trying to create a datatable or chart in Kibana using relational database data. The database table contains data about the transaction, or rather, the data about the steps in the transaction. But Logstash jdbc plugin adds each step of the transaction as a new document (for every row in the database). If I can group the documents to be indexed on Elasticsearch by column2, and if I can collect all the step information of the transaction in one document only, I can create the visualization I want, which corresponds to column2 in my example. I tried Logstash aggregate plugin, but the result not exactly I need. What do you think? How can I get that "one document which includes all the steps info of a transaction" document in a simple and effective way? Thank you for your help. ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [November 1, 2017, 8:58am UTC](https://discuss.elastic.co/t/combining-multiple-documents-based-on-id/105534/16 "2017-11-01T08:58:08Z")
The reason I asked is that Kibana currently does not support nested documents well and the examples you provided seemed to indicate a nested structure. As I do not know your data nor the different ways you need to query and analyse it, it is hard for me to recommend a structure. ---
**Author:** ![qube](https://avatars.discourse-cdn.com/v4/letter/q/ea666f/32.png) [@qube](https://discuss.elastic.co/u/qube)\ **Post date:** [November 1, 2017, 9:04am UTC](https://discuss.elastic.co/t/combining-multiple-documents-based-on-id/105534/17 "2017-11-01T09:04:12Z")
Okay then. Thank you very much for your help anyway! ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [November 1, 2017, 9:05am UTC](https://discuss.elastic.co/t/combining-multiple-documents-based-on-id/105534/18 "2017-11-01T09:05:32Z")
For some types of data you may benefit from storing the raw data (to serve some types of analysis) and also create an [entity-centric index](https://www.elastic.co/videos/entity-centric-indexing-mark-harwood) for other types of analysis. ---
**Author:** ![Deepti\_Jain](https://avatars.discourse-cdn.com/v4/letter/d/41988e/32.png) [@Deepti\_Jain](https://discuss.elastic.co/u/Deepti_Jain)\ **Post date:** [November 1, 2017, 3:28pm UTC](https://discuss.elastic.co/t/combining-multiple-documents-based-on-id/105534/19 "2017-11-01T15:28:49Z")
You can use jdbcinput plugin and provide the SQLserver query with the row selection criteria in the input section of logstash configuration file to fetch the data. ---
**Author:** ![qube](https://avatars.discourse-cdn.com/v4/letter/q/ea666f/32.png) [@qube](https://discuss.elastic.co/u/qube)\ **Post date:** [November 1, 2017, 4:12pm UTC](https://discuss.elastic.co/t/combining-multiple-documents-based-on-id/105534/20 "2017-11-01T16:12:55Z")
Thank you Christian\_Dahlqvist, I'll check your video! Deepti\_Jain, I don't get it. Do you have a working example? [Next page](https://discuss.elastic.co/t/combining-multiple-documents-based-on-id/105534.md?page=2)