# Combining multiple documents based on ID

**URL:** <https://discuss.elastic.co/t/combining-multiple-documents-based-on-id/105534>\
**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:** 3\
**Page:** 2

<div class="post-metadata">

**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 2, 2017, 6:07am UTC](https://discuss.elastic.co/t/combining-multiple-documents-based-on-id/105534/21 "2017-11-02T06:07:33Z")

</div>

I' m not sure if I got what you are looking for logstash to do. But going by the topic discussion I'm replying what I understand.

I do the following way to pull data in my ES index and structure it for convenience so that searches can be done in a simpler way.

input {  
jdbc {  
jdbc\_driver\_library =\> "path to the jdbc driver for the DB you are using. in my case it was sql"  
jdbc\_driver\_class =\> "Java::com.mysql.cj.jdbc.Driver"  
jdbc\_connection\_string =\> "jdbc:mysql:db connection string"  
jdbc\_user =\> "dbusername"  
jdbc\_password =\> "dbpassword"  
schedule =\> "\* \* \* \* \*"  
tags =\> "manufacturer\_product\_location\_map"  
statement =\> "SELECT product\_location\_id, location\_id, manufacturer\_brand\_id, product\_id,  
IF (map\_product\_location.modified\_date \>= map\_product\_manufacturer\_brand.modified\_date, map\_product\_location.modified\_date, map\_product\_manufacturer\_brand.modified\_date) modified\_date  
FROM rapp\_product.map\_product\_location INNER JOIN rapp\_product.map\_product\_manufacturer\_brand  
ON (map\_product\_location.product\_manufacturer\_brand\_id = map\_product\_manufacturer\_brand.product\_manufacturer\_brand\_id)  
where (( map\_product\_location.modified\_date is NULL || map\_product\_manufacturer\_brand.modified\_date is NULL)  
|| (map\_product\_location.modified\_date \> :sql\_last\_value || map\_product\_manufacturer\_brand.modified\_date \> :sql\_last\_value))"  
last\_run\_metadata\_path =\> "/logstash\_ret/.mnfctr\_prod\_locn\_map\_retailer\_ls\_jdbc\_last\_run"   
}  
}  
output {  
if "manufacturer\_product\_location\_map" in [tags] {  
elasticsearch {  
hosts =\> ["localhost:9200"]  
index =\> "es\_rapp\_product"  
document\_type =\> "manufacturer\_product\_location\_map"  
document\_id =\> "%{product\_location\_id}"  
}  
}  
}

So in the statement in jdbc section in input part of the logstash conf file we have the freedom to query the DB and the output of this is indexed into the ES in the "index" under the type document\_type and unique identifier is the document\_id as you can see in the output segment.

In ES it looks like below

{  
"\_index": "es\_rapp\_product",  
"\_type": "manufacturer\_product\_location\_map",  
"\_id": "14",  
"\_score": 1,  
"\_source": {  
"product\_location\_id": 14,  
"location\_id": 20,  
"manufacturer\_brand\_id": 1,  
"product\_id": 14,  
"modified\_date": null,  
"@version": "1",  
"@timestamp": "2017-11-01T13:48:00.282Z",  
"tags": [  
"manufacturer\_product\_location\_map"  
]  
}  
},  
{  
"\_index": "es\_rapp\_product",  
"\_type": "manufacturer\_product\_location\_map",  
"\_id": "19",  
"\_score": 1,  
"\_source": {  
"product\_location\_id": 19,  
"location\_id": 20,  
"manufacturer\_brand\_id": 1,  
"product\_id": 19,  
"modified\_date": null,  
"@version": "1",  
"@timestamp": "2017-11-01T13:48:00.282Z",  
"tags": [  
"manufacturer\_product\_location\_map"  
]  
}  
},  
{  
"\_index": "es\_rapp\_product",  
"\_type": "manufacturer\_product\_location\_map",  
"\_id": "22",  
"\_score": 1,  
"\_source": {  
"product\_location\_id": 22,  
"location\_id": 20,  
"manufacturer\_brand\_id": 1,  
"product\_id": 22,  
"modified\_date": null,  
"@version": "1",  
"@timestamp": "2017-11-01T13:48:00.283Z",  
"tags": [  
"manufacturer\_product\_location\_map"  
]  
}  
},

Hope this helps.

---

<div class="post-metadata">

**Author:** ![qube](https://avatars.discourse-cdn.com/v4/letter/q/ea666f/32.png) [@qube](https://discuss.elastic.co/u/qube)\
**Post date:** [November 6, 2017, 7:22am UTC](https://discuss.elastic.co/t/combining-multiple-documents-based-on-id/105534/22 "2017-11-06T07:22:34Z")

</div>

Hello Deepti\_Jain,

Your JDBC configuration looks like my configuration. There is not problem pulling data from SQL Server to Elasticsearch, it works fine.

Here my need is to combine all the documents into "one document" in the Elasticsearch index according to a column value. For example, with your data, do you know how to produce one document in the Elasticsearch depending on your "location\_id": 20 value? I see you're producing one document for every new row in the SQL Server.

I need to group all the documents coming from SQL Server into one document containing "location\_id": 20 value in it only.

---

<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:** [December 4, 2017, 7:22am UTC](https://discuss.elastic.co/t/combining-multiple-documents-based-on-id/105534/23 "2017-12-04T07:22:51Z")

</div>

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

[Previous page](https://discuss.elastic.co/t/combining-multiple-documents-based-on-id/105534.md?page=1)