# Denormalization through scripting (Painless or Groovy)

**URL:** <https://discuss.elastic.co/t/denormalization-through-scripting-painless-or-groovy/155313>\
**Category:** Elasticsearch\
**Created:** [November 4, 2018, 7:42pm UTC](https://discuss.elastic.co/t/denormalization-through-scripting-painless-or-groovy/155313 "2018-11-04T19:42:47Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![SusantaBN](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/susantabn/32/37273_2.png) [@SusantaBN](https://discuss.elastic.co/u/SusantaBN)\
**Post date:** [November 4, 2018, 7:42pm UTC](https://discuss.elastic.co/t/denormalization-through-scripting-painless-or-groovy/155313/1 "2018-11-04T19:42:47Z")

</div>

I am looking for ways to de-normalize elastic search indexes.

**Input Test Data**

Dept table  
 ![dept](https://us1.discourse-cdn.com/elastic/original/3X/e/0/e0cef3ed79d687fd569ab58b80d32e74c44f3de2.png)

Location Table  
 ![Location](https://us1.discourse-cdn.com/elastic/original/3X/d/d/dd0cbb3de9eb72ae1b6cc3c0fa2f069fe73dad7d.png)  
Expected Denormalized table  
 ![normalized](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1aaa3626735986f00cce7cac708c50e9c2eb2cc3.png)

One way to de-normalize is to de-normalize the data before ingesting the data to Elasticsearch.  
However, **I am looking for a way to de-normalize the data within elastic search either through Painless or Groovy scripting**. I hope this way will be more efficient.

Any help with sample script would be highly appreciated.  
**Input Dept Table**  
{  
"took": 1,  
"timed\_out": false,  
"\_shards": {  
"total": 5,  
"successful": 5,  
"skipped": 0,  
"failed": 0  
},  
"hits": {  
"total": 4,  
"max\_score": 1,  
"hits": [  
{  
"\_index": "dept",  
"\_type": "doc",  
"\_id": "0FsU4GYB7KWFLJIK6BFJ",  
"\_score": 1,  
"\_source": {  
"empid": "EMP001",  
"dept": "Manuf"  
}  
},  
{  
"\_index": "dept",  
"\_type": "doc",  
"\_id": "0VsU4GYB7KWFLJIK6BFJ",  
"\_score": 1,  
"\_source": {  
"empid": "EMP002",  
"dept": "Design"  
}  
},  
{  
"\_index": "dept",  
"\_type": "doc",  
"\_id": "01sU4GYB7KWFLJIK7BG3",  
"\_score": 1,  
"\_source": {  
"empid": "EMP004",  
"dept": "finance"  
}  
},  
{  
"\_index": "dept",  
"\_type": "doc",  
"\_id": "0lsU4GYB7KWFLJIK6BFJ",  
"\_score": 1,  
"\_source": {  
"empid": "EMP003",  
"dept": "Accounting"  
}  
}  
]  
}  
}

**Input Location Table**  
{  
"took": 1,  
"timed\_out": false,  
"\_shards": {  
"total": 5,  
"successful": 5,  
"skipped": 0,  
"failed": 0  
},  
"hits": {  
"total": 4,  
"max\_score": 1,  
"hits": [  
{  
"\_index": "location",  
"\_type": "doc",  
"\_id": "mVsi4GYB7KWFLJIKMxiO",  
"\_score": 1,  
"\_source": {  
"empid": "EMP001",  
"location": "Berlin"  
}  
},  
{  
"\_index": "location",  
"\_type": "doc",  
"\_id": "m1si4GYB7KWFLJIKOBgv",  
"\_score": 1,  
"\_source": {  
"empid": "EMP003",  
"location": "London"  
}  
},  
{  
"\_index": "location",  
"\_type": "doc",  
"\_id": "mFsi4GYB7KWFLJIKMxiO",  
"\_score": 1,  
"\_source": {  
"empid": "EMP002",  
"location": "Paris"  
}  
},  
{  
"\_index": "location",  
"\_type": "doc",  
"\_id": "mlsi4GYB7KWFLJIKMxiO",  
"\_score": 1,  
"\_source": {  
"empid": "EMP004",  
"location": "Barcelona"  
}  
}  
]  
}  
}

**Expected denormalized data**

{  
"took": 10,  
"timed\_out": false,  
"\_shards": {  
"total": 5,  
"successful": 5,  
"skipped": 0,  
"failed": 0  
},  
"hits": {  
"total": 4,  
"max\_score": 1,  
"hits": [  
{  
"\_index": "denormalized",  
"\_type": "doc",  
"\_id": "G1sp4GYB7KWFLJIKCBz8",  
"\_score": 1,  
"\_source": {  
**"empid": "EMP001",**  
**"location": "Berlin",**  
**"dept": "Manuf"**  
}  
},  
{  
"\_index": "denormalized",  
"\_type": "doc",  
"\_id": "HVsp4GYB7KWFLJIKDhyn",  
"\_score": 1,  
"\_source": {  
**"empid": "EMP004",**  
**"location": "Barcelona",**  
**"dept": "finance"**  
}  
},  
{  
"\_index": "denormalized",  
"\_type": "doc",  
"\_id": "HFsp4GYB7KWFLJIKCBz8",  
"\_score": 1,  
"\_source": {  
"empid": "EMP003",  
"location": "London",  
"dept": "Accounting"  
}  
},  
{  
"\_index": "denormalized",  
"\_type": "doc",  
"\_id": "Glsp4GYB7KWFLJIKCBz8",  
"\_score": 1,  
"\_source": {  
**"empid": "EMP002",**  
**"location": "Paris",**  
**"dept": "Design"**  
}  
}  
]  
}  
}

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [November 4, 2018, 7:56pm UTC](https://discuss.elastic.co/t/denormalization-through-scripting-painless-or-groovy/155313/2 "2018-11-04T19:56:22Z")

</div>

It's better IMHO to do that before sending documents to elasticsearch.  
You may want to look at Logstash if this suits your needs but if you already have an application which is generating the data, I'd try to do that transformation within the application.

---

<div class="post-metadata">

**Author:** ![SusantaBN](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/susantabn/32/37273_2.png) [@SusantaBN](https://discuss.elastic.co/u/SusantaBN)\
**Post date:** [November 4, 2018, 8:10pm UTC](https://discuss.elastic.co/t/denormalization-through-scripting-painless-or-groovy/155313/3 "2018-11-04T20:10:19Z")

</div>

Thanks a lot David for the prompt reply.

---

<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 2, 2018, 8:10pm UTC](https://discuss.elastic.co/t/denormalization-through-scripting-painless-or-groovy/155313/4 "2018-12-02T20:10:34Z")

</div>

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