# Lookup table for custom sorting

**URL:** <https://discuss.elastic.co/t/lookup-table-for-custom-sorting/7465>\
**Category:** Elasticsearch\
**Created:** [April 25, 2012, 10:27pm UTC](https://discuss.elastic.co/t/lookup-table-for-custom-sorting/7465 "2012-04-25T22:27:41Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Jack\_2](https://avatars.discourse-cdn.com/v4/letter/j/eb9ed0/32.png) [@Jack\_2](https://discuss.elastic.co/u/Jack_2)\
**Post date:** [April 25, 2012, 10:27pm UTC](https://discuss.elastic.co/t/lookup-table-for-custom-sorting/7465/1 "2012-04-25T22:27:41Z")

</div>

Hello,

I've searched the documentation and the internet, and I have some questions  
about normalized data / sorting in ES.

Our application has some normalized data in Elasticsearch, i.e. documents  
like

{ widgetNumber: 10, text: "Hello, World!" }  
{ widgetNumber: 10, text: "Qwerty12345" }  
{ widgetNumber: 99, text: "Blah." }

where widgetNumber: 10 corresponds to the string "foo" and widgetNumber:99  
corresponds to the string "bar".

We never need to do text searches on widget, and widget's value may be  
updated often, so we keep it normalized.

However, we do want to be able to sort results by widget's value. The  
approach I'm trying is to make a lookup table for widget so that the custom  
sort script would look like  
"n = doc['widgetNumber'].value; return lookup[n];"  
where "lookup" is a parameter to the script.

This works as expected, but it requires that you send the lookup table to  
ES. Is there a mechanism to store this table in ES for use in the script?  
Perhaps it is feasible with some small extensions/plugins?

Failing that, is there a different approach entirely that I should be using?

Many thanks,  
Jack

---

<div class="post-metadata">

**Author:** ![kimchy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kimchy/32/44952_2.png) [@kimchy](https://discuss.elastic.co/u/kimchy)\
**Post date:** [April 27, 2012, 9:12am UTC](https://discuss.elastic.co/t/lookup-table-for-custom-sorting/7465/2 "2012-04-27T09:12:00Z")

</div>

You can write a plugin that has a custom search script that does that. The  
script, on the factory level, can go to the external data that has hte  
mapping, and load it to memory, and then do the mapping, refreshing it now  
and then. There was a thread on the mailing list that did it and gisted a  
great sample for it, can't find it now...

On Thu, Apr 26, 2012 at 1:27 AM, Jack [jbnine1@gmail.com](mailto:jbnine1@gmail.com) wrote:

> Hello,
> 
> I've searched the documentation and the internet, and I have some  
> questions about normalized data / sorting in ES.
> 
> Our application has some normalized data in Elasticsearch, i.e. documents  
> like
> 
> { widgetNumber: 10, text: "Hello, World!" }  
> { widgetNumber: 10, text: "Qwerty12345" }  
> { widgetNumber: 99, text: "Blah." }
> 
> where widgetNumber: 10 corresponds to the string "foo" and widgetNumber:99  
> corresponds to the string "bar".
> 
> We never need to do text searches on widget, and widget's value may be  
> updated often, so we keep it normalized.
> 
> However, we do want to be able to sort results by widget's value. The  
> approach I'm trying is to make a lookup table for widget so that the custom  
> sort script would look like  
> "n = doc['widgetNumber'].value; return lookup[n];"  
> where "lookup" is a parameter to the script.
> 
> This works as expected, but it requires that you send the lookup table to  
> ES. Is there a mechanism to store this table in ES for use in the script?  
> Perhaps it is feasible with some small extensions/plugins?
> 
> Failing that, is there a different approach entirely that I should be  
> using?
> 
> Many thanks,  
> Jack

---

<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:** [July 6, 2017, 3:30am UTC](https://discuss.elastic.co/t/lookup-table-for-custom-sorting/7465/3 "2017-07-06T03:30:57Z")

</div>