# Combining documents in two indices correlated by their ID, but the fieldname is different **URL:** \ **Category:** Kibana\ **Tags:** transforms\ **Created:** [January 20, 2022, 10:47am UTC](https://discuss.elastic.co/t/combining-documents-in-two-indices-correlated-by-their-id-but-the-fieldname-is-different/294933 "2022-01-20T10:47:56Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![Spookies123](https://avatars.discourse-cdn.com/v4/letter/s/ee59a6/32.png) [@Spookies123](https://discuss.elastic.co/u/Spookies123)\ **Post date:** [January 20, 2022, 10:47am UTC](https://discuss.elastic.co/t/combining-documents-in-two-indices-correlated-by-their-id-but-the-fieldname-is-different/294933/1 "2022-01-20T10:47:56Z")
Hi! I am fairly new to the elastic stack, and i was wondering if something like the title suggests is possible. Say i have two types of documents, one for cars, and one for drivers. I want to combine the two documents into one by correlating between the driver's driverId and the car's ownerId. The values are the same, but the name is different. I have read a little about transforms, and was wondering if this would fit my case The reason i ask this, is because our system is event-driven, so if i were to do this the usual way done here, i would have to have a local id-lookup service, as well as some really wonky way to create a single document between the two, as each event does not represent a state of driver or car, but the different actions done to the state(driver created, driverName updated, or carModel changed for example). Any tips really would be helpful. Thanks ---
**Author:** ![Tomo\_M](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)\ **Post date:** [January 20, 2022, 11:08am UTC](https://discuss.elastic.co/t/combining-documents-in-two-indices-correlated-by-their-id-but-the-fieldname-is-different/294933/2 "2022-01-20T11:08:08Z")
Hi, Is it one-to-many relation? Driver can own multiple cars? Can you share sample data and sample desired output? It depends on the answer to the above question, [enrich policy](https://www.elastic.co/guide/en/elasticsearch/reference/current/enrich-processor.html) about driverID and 'enrich' the car index with owner/driver's information could be a possible solution. ---
**Author:** ![przemekwitek](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/przemekwitek/32/79526_2.png) [@przemekwitek](https://discuss.elastic.co/u/przemekwitek)\ **Post date:** [January 20, 2022, 11:09am UTC](https://discuss.elastic.co/t/combining-documents-in-two-indices-correlated-by-their-id-but-the-fieldname-is-different/294933/3 "2022-01-20T11:09:41Z")
> [@Tomo\_M](#): > > It depends on the answer to the above question Also, please share how would you like the documents to be combined. I.e. what kind of aggregation/script you'd use for combining? ---
**Author:** ![Spookies123](https://avatars.discourse-cdn.com/v4/letter/s/ee59a6/32.png) [@Spookies123](https://discuss.elastic.co/u/Spookies123)\ **Post date:** [January 20, 2022, 11:54am UTC](https://discuss.elastic.co/t/combining-documents-in-two-indices-correlated-by-their-id-but-the-fieldname-is-different/294933/4 "2022-01-20T11:54:59Z")
As far as i know this is a one-to-many relation yes. Here is an example Driver: driverId name country countryId Car: carId model modelId ownerId: same as driverId desired output: All fields of driver list of carId and models (or any form of this) This is just an example, anything that accomplishes the driver having information about what carId and models he has would do the job. Due to my lack of experience with elastic, i'm not sure what script/aggregation i would be using... ---
**Author:** ![Tomo\_M](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)\ **Post date:** [January 21, 2022, 10:00am UTC](https://discuss.elastic.co/t/combining-documents-in-two-indices-correlated-by-their-id-but-the-fieldname-is-different/294933/5 "2022-01-21T10:00:26Z")
Here is an enrich processar example. On caveat for this strategy is that you should always index car documents first and execute the enrich policy before you index the driver. ```auto PUT /test_driver/ { "mappings":{ "properties": { "driverId":{"type":"keyword"}, "name":{"type":"keyword"}, "country":{"type":"keyword"}, "countryId":{"type":"keyword"} } } } PUT /test_car/ { "mappings": { "properties": { "carId":{"type": "keyword"}, "model":{"type": "keyword"}, "modelId":{"type": "keyword"}, "ownerId":{"type": "keyword"} } } } POST /test_car/_bulk {"index":{}} {"carId":"car001","model":"model001","ownerId":"001"} {"index":{}} {"carId":"002","model":"model002","ownerId":"001"} PUT /_enrich/policy/test_car_policy { "match":{ "indices":"test_car", "match_field":"ownerId", "enrich_fields":["carId","model","modelId"] } } PUT /_enrich/policy/test_car_policy/_execute PUT /_ingest/pipeline/test_car_enrich { "description": "car enrich", "processors": [ { "enrich": { "policy_name": "test_car_policy", "field":"driverId", "target_field":"car", "max_matches":"128" } } ] } POST /_ingest/pipeline/test_car_enrich/_simulate { "docs":[ { "_index":"test_driver", "_source":{ "driverId":"001" } } ] } PUT /test_driver/_settings { "index":{ "default_pipeline": "test_car_enrich" } } GET /test_driver POST /test_driver/_bulk {"index":{}} {"driverId":"001","name":"foo"} {"index":{}} {"driverId":"002","name":"baa"} GET /test_driver/_search ``` ```auto { "hits" : { "hits" : [ { "_index" : "test_driver", "_type" : "_doc", "_id" : "pvQQfH4Bf0nakUP8c2Xp", "_score" : 1.0, "_source" : { "driverId" : "001", "car" : [ { "model" : "model001", "ownerId" : "001", "carId" : "car001" }, { "model" : "model002", "ownerId" : "001", "carId" : "002" } ], "name" : "foo" } }, { "_index" : "test_driver", "_type" : "_doc", "_id" : "p_QQfH4Bf0nakUP8c2Xp", "_score" : 1.0, "_source" : { "driverId" : "002", "name" : "baa" } } ] } } ``` ---
**Author:** ![Tomo\_M](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)\ **Post date:** [January 21, 2022, 10:18am UTC](https://discuss.elastic.co/t/combining-documents-in-two-indices-correlated-by-their-id-but-the-fieldname-is-different/294933/6 "2022-01-21T10:18:58Z")
Another way to do is using transform as you said. It may also fit your case. ---
**Author:** ![Spookies123](https://avatars.discourse-cdn.com/v4/letter/s/ee59a6/32.png) [@Spookies123](https://discuss.elastic.co/u/Spookies123)\ **Post date:** [January 21, 2022, 10:40am UTC](https://discuss.elastic.co/t/combining-documents-in-two-indices-correlated-by-their-id-but-the-fieldname-is-different/294933/7 "2022-01-21T10:40:45Z")
Thank you so much for taking the time, i will look into it! ---
**Author:** ![Tomo\_M](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)\ **Post date:** [January 21, 2022, 10:41am UTC](https://discuss.elastic.co/t/combining-documents-in-two-indices-correlated-by-their-id-but-the-fieldname-is-different/294933/8 "2022-01-21T10:41:52Z")
> [@Is is possible to partially update a dest doc with transforms?](https://discuss.elastic.co/t/is-is-possible-to-partially-update-a-dest-doc-with-transforms/209808/4): > > Hi, a transform can have multiple sources and they do not need a common schema as long as the field name of the group\_by is compatible. So if you have for example a field "customer\_id" in both indices "past\_purchases" and "predicted\_purchases" you can create a transform that joins over both indices: PUT \_transform/prediction\_accuracy { "source": { "index": ["past\_purchases", "predicted\_purchases"] }, "pivot": { "group\_by": { "id": {"terms": { "field": "customer\_id" … > [@How to aggregate data on elastic sent by logstash](https://discuss.elastic.co/t/how-to-aggregate-data-on-elastic-sent-by-logstash/205140/2): > > Hi, you should be able to do this using transform (see [https://www.elastic.co/guide/en/elasticsearch/reference/7.4/put-transform.html](https://www.elastic.co/guide/en/elasticsearch/reference/7.4/put-transform.html)). The workflow would look like this: you index the data into elasticsearch as single docs, this is the "source" index you create a transform that pulls data from the "source" and aggregates it according to your needs into a "dest" index. In your case you would group by entity Id, firm id, etc. and define aggregations, e.g. lastUpdated would be a max aggregatio… > [@How to join two index](https://discuss.elastic.co/t/how-to-join-two-index/274572/3): > > I like to add another option: [transform](https://www.elastic.co/guide/en/elasticsearch/reference/current/transforms.html). A transform in a nutshell is a task that runs aggregation queries and persists the result in an index. In order to join 2 indices you need compatible mappings for grouping, e.g. a key that is named the same way and has the same type. Note, this can be achieved with scripts or runtime fields, however if you want to run in it continuously at scale, it is advised to use proper ordinary mappings. For the group\_by use terms on the common key. For the aggreg… To use transform to join multiple indices is not straight forward, but these posts will help you to use transform. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [February 18, 2022, 10:42am UTC](https://discuss.elastic.co/t/combining-documents-in-two-indices-correlated-by-their-id-but-the-fieldname-is-different/294933/9 "2022-02-18T10:42:14Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.