# Merging two indexes by a common field **URL:** \ **Category:** Elasticsearch\ **Created:** [August 10, 2017, 9:12am UTC](https://discuss.elastic.co/t/merging-two-indexes-by-a-common-field/96576 "2017-08-10T09:12:49Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![marwa](https://avatars.discourse-cdn.com/v4/letter/m/67e7ee/32.png) [@marwa](https://discuss.elastic.co/u/marwa)\ **Post date:** [August 10, 2017, 9:12am UTC](https://discuss.elastic.co/t/merging-two-indexes-by-a-common-field/96576/1 "2017-08-10T09:12:49Z")
Hi, I have an index named 'transactions' and an other one called 'costs' the transactions indexe contains _{ product\_id: "1111", price\_unit: "23.56", customer\_name:"Marda Elbin" }_ the costs index contains the id of each product with its costs I want to merge both indexes based on the product\_id or to make a join betwen them based on the same field (product\_id) My purpose is the use expression language to calculate the revenues - costs for each product thus i used aliases 🙂 POST \_aliases { "actions" : [ { "add" : { "index" : "costs", "alias" : "alias1" } }, { "add" : { "index" : "transactions", "alias" : "alias1" } } ] } then i tryed to calculate revenues - costs with this query GET alias1/\_search { "query" : { "match\_all": {} }, "script\_fields" : { "test1" : { "script" : { "lang": "painless", "inline": "int gross\_profit\_margin = 0; if (doc[product\_identifier].value == doc['product\_id'].value){ gross\_profit\_margin + = (doc['product\_price'].value \* doc['quantity'].value \* (1 - doc['discount'].value /100)) - (doc['average\_marketing\_cost'].value + doc['average\_promotional\_cost'].value + doc['direct\_and\_indirect\_cost'].value ) return gross\_profit\_margin }" } } } } this error appears : { "error": { "root\_cause": [ { "type": "script\_exception", "reason": "compile error", "script\_stack": [ "... ){ gross\_profit\_margin + = (doc['product\_price'].v ...", " ^---- HERE" ], "script": "int gross\_profit\_margin = 0; if (doc[product\_identifier].value == doc['product\_id'].value){ gross\_profit\_margin + = (doc['product\_price'].value \* doc['quantity'].value \* (1 - doc['discount'].value /100)) - (doc['average\_marketing\_cost'].value + doc['average\_promotional\_cost'].value + doc['direct\_and\_indirect\_cost'].value ) return gross\_profit\_margin }", "lang": "painless" } ], "type": "search\_phase\_execution\_exception", "reason": "all shards failed", "phase": "query", "grouped": true, so my questions are 1. How can i merge indexes by a common fields 2. does aliases allow to do so Thenk you?? ---
**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\ **Post date:** [August 10, 2017, 9:28am UTC](https://discuss.elastic.co/t/merging-two-indexes-by-a-common-field/96576/2 "2017-08-10T09:28:56Z")
The context of your script's execution is accessing the properties of a single doc from a single index. Your script would not be able to simultaneously see doc1 from transactions index and doc 2 from products index. You can use the scroll api to read across 2 indices (no alias required) sorting on a common key and your application code would have to process the stream of interleaved results that are sorted on product ID. It would buffer a product's costs, applying them to the next transactions that share the common key. The newly enhanced transactions could then be inserted using the bulk API into a new index. Of course this only works if a transaction is for exactly one product. If your "transaction" docs are more like orders with \>1 product type then this approach clearly won't work as individual orders can't be sorted sensibly by a single key. In this scenario you'd have to do point queries on your product store to retrieve costs (a cache would obviously help here). ---
**Author:** ![marwa](https://avatars.discourse-cdn.com/v4/letter/m/67e7ee/32.png) [@marwa](https://discuss.elastic.co/u/marwa)\ **Post date:** [August 10, 2017, 9:42am UTC](https://discuss.elastic.co/t/merging-two-indexes-by-a-common-field/96576/3 "2017-08-10T09:42:27Z")
> [@Mark\_Harwood](#): > > It would buffer a product’s costs, applying them to the next transactions that share the common key. The newly enhanced transactions could then be inserted using the bulk API into a new index. I didn't not get what you really mean .Can you explain more to me please thank you ---
**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\ **Post date:** [August 10, 2017, 9:49am UTC](https://discuss.elastic.co/t/merging-two-indexes-by-a-common-field/96576/4 "2017-08-10T09:49:50Z")
> [@marwa](#): > > .Can you explain more to me please Perhaps it makes sense to clarify the requirement before we clarify the solution: - Do any of your "transaction" docs contain \>1 product? - Are you after a single number for margin across all products or per-product margins? - Are you doing this calculation regularly? - Should profit margins be measured as product-cost-at-time-of-sale or based on currently recorded product cost? - How many products and transactions do you have? ---
**Author:** ![marwa](https://avatars.discourse-cdn.com/v4/letter/m/67e7ee/32.png) [@marwa](https://discuss.elastic.co/u/marwa)\ **Post date:** [August 10, 2017, 9:57am UTC](https://discuss.elastic.co/t/merging-two-indexes-by-a-common-field/96576/5 "2017-08-10T09:57:27Z")
- for each transaction i have only one product - it would be better to calculate the margin per transaction not per product because for each transaction , we have a product sold with a specific quantity - yes i will do it regularly - the profit margin will be calculated with the currently recorded product cost which is stored in the costs index (i didn't specify the time of the cost of a product , in the index costs i have only product id and its cost nothing more) - i have 1055 products and more than 30000 transactions thank you ---
**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\ **Post date:** [August 10, 2017, 10:08am UTC](https://discuss.elastic.co/t/merging-two-indexes-by-a-common-field/96576/6 "2017-08-10T10:08:59Z")
> [@marwa](#): > > yes i will do it regularly Assuming you want to store costs/profit the best suggestion would be to fix your data "on the way in" if possible. 1,000 products is not a lot of data to keep in a RAM cache and lookup as you insert transaction data. Logstash I believe has some "lookup" type features that could help with this (best to ask in that forum). Advantages to doing it this way rather than a batch fix-later scheme is 1. costs would be recorded with the values current at point-of-sale 2. there is no lag between the logging of transactions and costing of transactions. ---
**Author:** ![marwa](https://avatars.discourse-cdn.com/v4/letter/m/67e7ee/32.png) [@marwa](https://discuss.elastic.co/u/marwa)\ **Post date:** [August 10, 2017, 10:12am UTC](https://discuss.elastic.co/t/merging-two-indexes-by-a-common-field/96576/7 "2017-08-10T10:12:54Z")
So you are suggesting that i remodel my data and to restore both indexes in one index ??? ---
**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\ **Post date:** [August 10, 2017, 10:16am UTC](https://discuss.elastic.co/t/merging-two-indexes-by-a-common-field/96576/8 "2017-08-10T10:16:00Z")
> [@marwa](#): > > So you are suggesting that i remodel my data and to restore both indexes in one index ??? Denormalization for the win. [Denormalizing Your Data | Elasticsearch: The Definitive Guide [2.x] | Elastic](https://www.elastic.co/guide/en/elasticsearch/guide/current/denormalization.html) ---
**Author:** ![marwa](https://avatars.discourse-cdn.com/v4/letter/m/67e7ee/32.png) [@marwa](https://discuss.elastic.co/u/marwa)\ **Post date:** [August 10, 2017, 10:28am UTC](https://discuss.elastic.co/t/merging-two-indexes-by-a-common-field/96576/9 "2017-08-10T10:28:44Z")
So i have to denormalize all the data i have manually ? i am sorry but i could'nt figure out what am i supposed to do in my case because denormalizing the data demands that for each transaction i will look for the product id with a query ? ---
**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\ **Post date:** [August 10, 2017, 10:42am UTC](https://discuss.elastic.co/t/merging-two-indexes-by-a-common-field/96576/10 "2017-08-10T10:42:23Z")
> [@marwa](#): > > because denormalizing the data demands that for each transaction i will look for the product id with a query ? This is a pretty common sort of enrichment task that any number of ETL tools support. Personally for a problem this small I'd opt for some custom Python and use a dict as a cache for product info. Like I said, Logstash has some of this data enrichment logic but it looks like a [smarter solution with caching is still some way off](https://github.com/elastic/logstash/issues/5221) . Either way, here is probably not the forum to discuss approaches further as this is somewhat "upstream" of core elasticsearch. ---
**Author:** ![marwa](https://avatars.discourse-cdn.com/v4/letter/m/67e7ee/32.png) [@marwa](https://discuss.elastic.co/u/marwa)\ **Post date:** [August 10, 2017, 12:01pm UTC](https://discuss.elastic.co/t/merging-two-indexes-by-a-common-field/96576/11 "2017-08-10T12:01:42Z")
Just a final question :can i store both data sets in one index but a different types (is it possible when both of the data sets has diferent mapping ) (noticing i have to change the product\_id in one of these data sets into an other name) then use painless to test if documents has the same product identifier if the condition is verified i calculate the margin profit ?? ---
**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\ **Post date:** [August 10, 2017, 12:04pm UTC](https://discuss.elastic.co/t/merging-two-indexes-by-a-common-field/96576/12 "2017-08-10T12:04:36Z")
No because of my first comment: > [@Mark\_Harwood](#): > > The context of your script’s execution is accessing the properties of a single doc from a single index. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [September 7, 2017, 12:04pm UTC](https://discuss.elastic.co/t/merging-two-indexes-by-a-common-field/96576/13 "2017-09-07T12:04:55Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.