# Storing table-like data in Elastic Search **URL:** \ **Category:** Elasticsearch\ **Created:** [November 15, 2012, 9:24pm UTC](https://discuss.elastic.co/t/storing-table-like-data-in-elastic-search/9732 "2012-11-15T21:24:22Z")\ **Posts on this page:** 16\ **Page:** 1
**Author:** ![haizaar](https://avatars.discourse-cdn.com/v4/letter/h/3d9bf3/32.png) [@haizaar](https://discuss.elastic.co/u/haizaar)\ **Post date:** [November 15, 2012, 9:24pm UTC](https://discuss.elastic.co/t/storing-table-like-data-in-elastic-search/9732/1 "2012-11-15T21:24:22Z")
Hi! I'm a ElasticSearch newbie and approaching the following project - I have many millions (100+ and counting) of tables in JSON format like this: { "title" : "dogs species", "col\_names" : ["name", "description", "country\_of\_origin"], "rows" : { ["Boxer", "good dog", "Germany"], ["Irish Setter", "great dog", "Irland"] } } { "title" : "Classmates", "col\_names" : ["name", "class", "age", "avg\_grade"], "rows" : { ["Alice", "A", "14", "85"], ["Bob", "B", "15", "91"] } { "title" : "Misc stuff", "col\_names" : ["foo"] "rows" : { ["Setter is impotant"], ["Irland is green"] } I.e. tons of completely unrelated structural data. My goal is to make it searchable. My search requirements are: - Just search for text inside the cells over all tables. I.e. searching for "Boxer Irland" should find the first and last table above - Maching withing the row should have a higher score. I.e. searching for "Irland Setter" should give the first table the higher score in results - Its also important to somehow preseve the data structure of each table, so it could be fetched and converted back to structured JSON Any ideas on how to approach this problem? Thank you all very much in advance. Zaar -- ---
**Author:** ![otisg](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/otisg/32/492_2.png) [@otisg](https://discuss.elastic.co/u/otisg)\ **Post date:** [November 16, 2012, 5:10am UTC](https://discuss.elastic.co/t/storing-table-like-data-in-elastic-search/9732/2 "2012-11-16T05:10:38Z")
Hi, If you can transform your JSON just a little bit, you will be able to throw is at ES almost as is. Have a look at JSON on, say, [Elastic โ€” The Search AI Company | Elastic](http://www.elasticsearch.org/guide/reference/api/index_.html) to see what I mean. ## Otis Search Analytics - [Cloud Monitoring Tools & Services | Sematext](http://sematext.com/search-analytics/index.html) Performance Monitoring - [Sematext Monitoring | Infrastructure Monitoring Service](http://sematext.com/spm/index.html) On Thursday, November 15, 2012 4:24:22 PM UTC-5, Zaar Hai wrote: > Hi! > > I'm a Elasticsearch newbie and approaching the following project - I have > many millions (100+ and counting) of tables in JSON format like this: > > { "title" : "dogs species", > "col\_names" : ["name", "description", "country\_of\_origin"], > "rows" : { ["Boxer", "good dog", "Germany"], > ["Irish Setter", "great dog", "Irland"] > } > } > > { "title" : "Classmates", > "col\_names" : ["name", "class", "age", "avg\_grade"], > "rows" : { ["Alice", "A", "14", "85"], > ["Bob", "B", "15", "91"] > } > > { "title" : "Misc stuff", > "col\_names" : ["foo"] > "rows" : { ["Setter is impotant"], > ["Irland is green"] > } > > I.e. tons of completely unrelated structural data. My goal is to make it > searchable. > My search requirements are: > > - Just search for text inside the cells over all tables. I.e. > searching for "Boxer Irland" should find the first and last table above > - Maching withing the row should have a higher score. I.e. searching > for "Irland Setter" should give the first table the higher score in results > - Its also important to somehow preseve the data structure of each > table, so it could be fetched and converted back to structured JSON > > Any ideas on how to approach this problem? > > Thank you all very much in advance. > > Zaar -- ---
**Author:** ![haizaar](https://avatars.discourse-cdn.com/v4/letter/h/3d9bf3/32.png) [@haizaar](https://discuss.elastic.co/u/haizaar)\ **Post date:** [November 16, 2012, 1:46pm UTC](https://discuss.elastic.co/t/storing-table-like-data-in-elastic-search/9732/3 "2012-11-16T13:46:50Z")
Hi Otis, Are you sure you gave me the right link? Can you please be more specific regarding how to transform? - pull all of the cell data as a single field?, but then I'll lost the table structure... Zaar. On Friday, November 16, 2012 7:10:38 AM UTC+2, Otis Gospodnetic wrote: > Hi, > > If you can transform your JSON just a little bit, you will be able to > throw is at ES almost as is. Have a look at JSON on, say, > [Elasticsearch Platform โ€” Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/reference/api/index_.html) to see what > I mean. > > ## Otis > > Search Analytics - [Cloud Monitoring Tools & Services | Sematext](http://sematext.com/search-analytics/index.html) > Performance Monitoring - [Sematext Monitoring | Infrastructure Monitoring Service](http://sematext.com/spm/index.html) > > On Thursday, November 15, 2012 4:24:22 PM UTC-5, Zaar Hai wrote: > > > Hi! > > > > I'm a Elasticsearch newbie and approaching the following project - I have > > many millions (100+ and counting) of tables in JSON format like this: > > > > { "title" : "dogs species", > > "col\_names" : ["name", "description", "country\_of\_origin"], > > "rows" : { ["Boxer", "good dog", "Germany"], > > ["Irish Setter", "great dog", "Irland"] > > } > > } > > > > { "title" : "Classmates", > > "col\_names" : ["name", "class", "age", "avg\_grade"], > > "rows" : { ["Alice", "A", "14", "85"], > > ["Bob", "B", "15", "91"] > > } > > > > { "title" : "Misc stuff", > > "col\_names" : ["foo"] > > "rows" : { ["Setter is impotant"], > > ["Irland is green"] > > } > > > > I.e. tons of completely unrelated structural data. My goal is to make it > > searchable. > > My search requirements are: > > > > - Just search for text inside the cells over all tables. I.e. > > searching for "Boxer Irland" should find the first and last table above > > - Maching withing the row should have a higher score. I.e. searching > > for "Irland Setter" should give the first table the higher score in results > > - Its also important to somehow preseve the data structure of each > > table, so it could be fetched and converted back to structured JSON > > > > Any ideas on how to approach this problem? > > > > Thank you all very much in advance. > > > > Zaar -- ---
**Author:** ![Filirom1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/filirom1/32/2234_2.png) [@Filirom1](https://discuss.elastic.co/u/Filirom1)\ **Post date:** [November 16, 2012, 2:49pm UTC](https://discuss.elastic.co/t/storing-table-like-data-in-elastic-search/9732/4 "2012-11-16T14:49:44Z")
For a csv table liek this one: key1, key2, key3 val1, val2, val3 val11, val12, val13 this csv should be transformed into [{ "key1": "val1", "key2": "val2", "key3": "val3", },{ "key1": "val11", "key2": "val12", "key3": "val13", }] to be sent to Elasticsearch. 2012/11/16 Zaar Hai [haizaar@gmail.com](mailto:haizaar@gmail.com) > Hi Otis, > > Are you sure you gave me the right link? > > Can you please be more specific regarding how to transform? - pull all of > the cell data as a single field?, but then I'll lost the table structure... > > Zaar. > > On Friday, November 16, 2012 7:10:38 AM UTC+2, Otis Gospodnetic wrote: > > > Hi, > > > > If you can transform your JSON just a little bit, you will be able to > > throw is at ES almost as is. Have a look at JSON on, say, > > [http://www.elasticsearch](http://www.elasticsearch). **org/guide/reference/api/index\_**.html[http://www.elasticsearch.org/guide/reference/api/index\_.html](http://www.elasticsearch.org/guide/reference/api/index_.html)to see what I mean. > > > > ## Otis > > > > Search Analytics - [http://sematext.com/search-\*\*analytics/index.html](http://sematext.com/search-**analytics/index.html)[http://sematext.com/search-analytics/index.html](http://sematext.com/search-analytics/index.html) > > Performance Monitoring - [Sematext Monitoring | Infrastructure Monitoring Service](http://sematext.com/spm/**index.html)[http://sematext.com/spm/index.html](http://sematext.com/spm/index.html) > > > > On Thursday, November 15, 2012 4:24:22 PM UTC-5, Zaar Hai wrote: > > > > > Hi! > > > > > > I'm a Elasticsearch newbie and approaching the following project - I > > > have many millions (100+ and counting) of tables in JSON format like this: > > > > > > { "title" : "dogs species", > > > "col\_names" : ["name", "description", "country\_of\_origin"], > > > "rows" : { ["Boxer", "good dog", "Germany"], > > > ["Irish Setter", "great dog", "Irland"] > > > } > > > } > > > > > > { "title" : "Classmates", > > > "col\_names" : ["name", "class", "age", "avg\_grade"], > > > "rows" : { ["Alice", "A", "14", "85"], > > > ["Bob", "B", "15", "91"] > > > } > > > > > > { "title" : "Misc stuff", > > > "col\_names" : ["foo"] > > > "rows" : { ["Setter is impotant"], > > > ["Irland is green"] > > > } > > > > > > I.e. tons of completely unrelated structural data. My goal is to make it > > > searchable. > > > My search requirements are: > > > > > > - Just search for text inside the cells over all tables. I.e. > > > searching for "Boxer Irland" should find the first and last table above > > > - Maching withing the row should have a higher score. I.e. > > > searching for "Irland Setter" should give the first table the higher score > > > in results > > > - Its also important to somehow preseve the data structure of each > > > table, so it could be fetched and converted back to structured JSON > > > > > > Any ideas on how to approach this problem? > > > > > > Thank you all very much in advance. > > > > > > Zaar > > > > > > -- -- ---
**Author:** ![Clinton\_Gormley](https://avatars.discourse-cdn.com/v4/letter/c/50afbb/32.png) [@Clinton\_Gormley](https://discuss.elastic.co/u/Clinton_Gormley)\ **Post date:** [November 16, 2012, 3:03pm UTC](https://discuss.elastic.co/t/storing-table-like-data-in-elastic-search/9732/5 "2012-11-16T15:03:18Z")
> this csv should be transformed into > [{ > "key1": "val1", > "key2": "val2", > > "key3": "val3", > > },{ > "key1": "val11", > "key2": "val12", > > "key3": "val13", > > }] I agree that the above would be ideal, but Zaar has explained that his data has arbitrary column names, so he may end up with massive mappings. Zaar: your documents would be indexed like this: { "title" : [dogs, species], "col\_names" : [name, description, country\_of\_origin], "rows": [boxer, good, dog, germany, irish, setter, great, dog, irland] } so you can search the "rows" field for any of those terms, and you can use a match\_phrase query with a highish "slop" value to boost terms that are closer together, eg "boxer good" would score higher than "boxer dog" clint -- ---
**Author:** ![haizaar](https://avatars.discourse-cdn.com/v4/letter/h/3d9bf3/32.png) [@haizaar](https://discuss.elastic.co/u/haizaar)\ **Post date:** [November 16, 2012, 5:42pm UTC](https://discuss.elastic.co/t/storing-table-like-data-in-elastic-search/9732/6 "2012-11-16T17:42:25Z")
Clinton, so if I understand you correctly, you suggest transforming my first example to the following: { "title": "dogs species", "col\_names": "["name", "description", "country\_of\_origin"]", "rows": "[[ "Boxer", "good dog", "Germany"], [ "Irish Setter", "great dog", "Irland" ] ]" } That actually makes sence! I've throwed it in and default analyzer does a good job harversing words only: curl -s -X GET "[http://localhost:9200/test2/\_analyze?pretty=true](http://localhost:9200/test2/_analyze?pretty=true)" -d '[ [ "Boxer", "good dog", "Germany" ], [ "Irish Setter", "great dog", "Irland" ] ]' | grep token "tokens" : [ { "token" : "boxer", "token" : "good", "token" : "dog", "token" : "germany", "token" : "irish", "token" : "setter", "token" : "great", "token" : "dog", "token" : "irland", It also stores valid json lists as values of "rows" and "col\_names" fields, so I can easyly construct a structured data from the query results. Thank you! Zaar On Friday, November 16, 2012 5:03:27 PM UTC+2, Clinton Gormley wrote: > > this csv should be transformed into > > > [{ > > "key1": "val1", > > "key2": "val2", > > > > "key3": "val3", > > > > },{ > > "key1": "val11", > > "key2": "val12", > > > > "key3": "val13", > > > > }] > > I agree that the above would be ideal, but Zaar has explained that his > data has arbitrary column names, so he may end up with massive mappings. > > Zaar: your documents would be indexed like this: > > { "title" : [dogs, species], > "col\_names" : [name, description, country\_of\_origin], > "rows": [boxer, good, dog, germany, irish, setter, great, dog, irland] > } > > so you can search the "rows" field for any of those terms, and you can > use a match\_phrase query with a highish "slop" value to boost terms that > are closer together, eg "boxer good" would score higher than "boxer dog" > > clint -- ---
**Author:** ![haizaar](https://avatars.discourse-cdn.com/v4/letter/h/3d9bf3/32.png) [@haizaar](https://discuss.elastic.co/u/haizaar)\ **Post date:** [November 16, 2012, 6:55pm UTC](https://discuss.elastic.co/t/storing-table-like-data-in-elastic-search/9732/7 "2012-11-16T18:55:23Z")
Actually I've spotted that Elastic search supports arrays (but not tested array). So I've came up with the model as below. However I'm now strugging how to give priority to the matching from the same row. I.e. currently text search for "Irland Setter" gives second document much higher score (0.21 and 0.13 respectively). I need the first document to have a higher score because it has both "Irand" and "Setter" in the same row. { "title": "dogs species", "col\_names": ["name", "description", "country\_of\_origin"], "rows": [ { "row": ["Boxer", "good dog", "Germany"] }, { "row": ["Irish Setter", "great dog", "Irland"] } ] } { "title": "Misc stuff", "col\_names": ["foo"], "rows": [ { "row": ["Setter is impotant"] }, { "row": ["Irland is green"] } ] } Zaar On Friday, November 16, 2012 7:42:25 PM UTC+2, Zaar Hai wrote: > Clinton, > so if I understand you correctly, you suggest transforming my first > example to the following: > > { > "title": "dogs species", > "col\_names": "["name", "description", "country\_of\_origin"]", > "rows": "[[ "Boxer", "good dog", "Germany"], [ "Irish > Setter", "great dog", "Irland" ] ]" > } > > That actually makes sence! I've throwed it in and default analyzer does a > good job harversing words only: > curl -s -X GET "[http://localhost:9200/test2/\_analyze?pretty=true](http://localhost:9200/test2/_analyze?pretty=true)" -d '[ > ["Boxer", "good dog", "Germany"], [ "Irish Setter", "great > dog", "Irland" ] ]' | grep token > "tokens" : [ { > "token" : "boxer", > "token" : "good", > "token" : "dog", > "token" : "germany", > "token" : "irish", > "token" : "setter", > "token" : "great", > "token" : "dog", > "token" : "irland", > > It also stores valid json lists as values of "rows" and "col\_names" > fields, so I can easyly construct a structured data from the query results. > > Thank you! > Zaar > > On Friday, November 16, 2012 5:03:27 PM UTC+2, Clinton Gormley wrote: > > > > this csv should be transformed into > > > > > [{ > > > "key1": "val1", > > > "key2": "val2", > > > > > > "key3": "val3", > > > > > > },{ > > > "key1": "val11", > > > "key2": "val12", > > > > > > "key3": "val13", > > > > > > }] > > > > I agree that the above would be ideal, but Zaar has explained that his > > data has arbitrary column names, so he may end up with massive mappings. > > > > Zaar: your documents would be indexed like this: > > > > { "title" : [dogs, species], > > "col\_names" : [name, description, country\_of\_origin], > > "rows": [boxer, good, dog, germany, irish, setter, great, dog, irland] > > } > > > > so you can search the "rows" field for any of those terms, and you can > > use a match\_phrase query with a highish "slop" value to boost terms that > > are closer together, eg "boxer good" would score higher than "boxer dog" > > > > clint -- ---
**Author:** ![Clinton\_Gormley](https://avatars.discourse-cdn.com/v4/letter/c/50afbb/32.png) [@Clinton\_Gormley](https://discuss.elastic.co/u/Clinton_Gormley)\ **Post date:** [November 17, 2012, 12:23pm UTC](https://discuss.elastic.co/t/storing-table-like-data-in-elastic-search/9732/8 "2012-11-17T12:23:55Z")
> However I'm now strugging how to give priority to the matching from > the same row. I.e. currently text search for "Irland Setter" gives > second document much higher score (0.21 and 0.13 respectively). First, you're experimenting with very few documents (I assume) which means that your terms are unevenly distributed across your shards. For testing purposes, I would either add "search\_type=dfs\_query\_then\_fetch" to your search query string, or I would create a test index with only 1 shard. > I need the first document to have a higher score because it has both > "Irand" and "Setter" in the same row. Use the match\_phrase query with a high "slop" value, eg: ``` { "query": { "match_phrase": { "row": { "query": "irland setter", "slop": 100 } } }} ``` This will incorporate token distance into the relevance calculation. Also, when you're indexing arrays of analyzed strings, it may be worth setting the position\_offset\_gap in the mapping. If you index ["quick brown", "fox"], by default it would be indexed as: - position 1 : quick - position 2 : brown - position 3 : fox If you set the positon\_offset\_gap, ie map the "row" field as: { type: "string", position\_offset\_gap: 100 } it would be indexed as: - position 1 : quick - position 2 : brown - position 103 : fox This of course depends on what you are trying to achieve with your data. clint -- ---
**Author:** ![haizaar](https://avatars.discourse-cdn.com/v4/letter/h/3d9bf3/32.png) [@haizaar](https://discuss.elastic.co/u/haizaar)\ **Post date:** [November 17, 2012, 2:51pm UTC](https://discuss.elastic.co/t/storing-table-like-data-in-elastic-search/9732/9 "2012-11-17T14:51:18Z")
On Saturday, November 17, 2012 2:24:02 PM UTC+2, Clinton Gormley wrote: > First, you're experimenting with very few documents (I assume) which > means that your terms are unevenly distributed across your shards. For > testing purposes, I would either add "search\_type=dfs\_query\_then\_fetch" > to your search query string, or I would create a test index with only 1 > shard. Yes, I'm currently experimenting just with two documents to make sure I'm on the right track. I've recreated them on a single shard following your advice > > I need the first document to have a higher score because it has both > > "Irand" and "Setter" in the same row. > > Use the match\_phrase query with a high "slop" value, eg: > > ``` > { "query": { > "match_phrase": { > "row": { > "query": "irland setter", > "slop": 100 > } > } > }} > > ``` This does not help. The "wrong" (second) document still gets much higher score. I think its because after analysis, the first document looks like: "boxer", "good", "dog", "germany", "irish", "setter", "great", "dog", "irland" And the second: "setter", "important", "irland", "green" So in the second document the "setter" is actually closer to "irland" then in the first one. > Also, when you're indexing arrays of analyzed strings, it may be worth > setting the position\_offset\_gap in the mapping. > > If you index ["quick brown", "fox"], by default it would be indexed as: > > - position 1 : quick > - position 2 : brown > - position 3 : fox > > If you set the positon\_offset\_gap, ie map the "row" field as: > > { type: "string", position\_offset\_gap: 100 } > > it would be indexed as: > > - position 1 : quick > - position 2 : brown > - position 103 : fox > > This of course depends on what you are trying to achieve with your data. This looks like an interesting approach. However I need gaps between rows and not between row members. Strangely enough, changing mapping for "row" as you've suggested, caused no results at all. Also running an analyzer shows that position\_offset\_gap is disregarded completely. Here is my query: { "query": { "match\_phrase": { "row": { "query": "setter ireland", "slop":100 } } } } And here is my mapping: { "table" : { "properties" : { "title" : {"type" : "string"}, "col\_names" : {"type" : "string"}, "rows" : { "properties" : { "row" : { "type" : "string", "position\_offset\_gap" : 100 } } } } } } Thank you very much for your help and time! Zaar > clint -- ---
**Author:** ![Clinton\_Gormley](https://avatars.discourse-cdn.com/v4/letter/c/50afbb/32.png) [@Clinton\_Gormley](https://discuss.elastic.co/u/Clinton_Gormley)\ **Post date:** [November 17, 2012, 3:11pm UTC](https://discuss.elastic.co/t/storing-table-like-data-in-elastic-search/9732/10 "2012-11-17T15:11:42Z")
> This does not help. The "wrong" (second) document still gets much > higher score. > I think its because after analysis, the first document looks like: > "boxer", "good", "dog", "germany", "irish", "setter", "great", > "dog", "irland" > And the second: > "setter", "important", "irland", "green" Ah right, yes. And probably the fact that that row is shorter makes it appear to be more relevant. You could try setting omit\_norms to true, to ignore field length normalization. > **[Elasticsearch Platform โ€” Find real-time answers at scale](https://www.elastic.co)** > > Power insights and outcomes with the Elasticsearch Platform and AI. See into your data and find answers that matter with enterprise solutions designed to help you build, observe, and protect. Try Elasticsearch free today. > This looks like an interesting approach. However I need gaps between > rows and not between row members. True, sorry! you may want to try an approach where you make "rows" type "nested". So that would store each "row" as a separate sub-document, which you could query individually. Then you can also add {include\_in\_root: true} to the "rows" mapping, so that all the data would also be indexed in the root document. I've put together a demo here: > clint -- ---
**Author:** ![haizaar](https://avatars.discourse-cdn.com/v4/letter/h/3d9bf3/32.png) [@haizaar](https://discuss.elastic.co/u/haizaar)\ **Post date:** [November 17, 2012, 4:50pm UTC](https://discuss.elastic.co/t/storing-table-like-data-in-elastic-search/9732/11 "2012-11-17T16:50:55Z")
On Saturday, November 17, 2012 5:11:50 PM UTC+2, Clinton Gormley wrote: > > This looks like an interesting approach. However I need gaps between > > rows and not between row members. > > True, sorry! > > you may want to try an approach where you make "rows" type "nested". So > that would store each "row" as a separate sub-document, which you could > query individually. > > Then you can also add {include\_in\_root: true} to the "rows" mapping, so > that all the data would also be indexed in the root document. > > I've put together a demo here: > > [Nested documents ยท GitHub](https://gist.github.com/4096675) Wow! It works! Thank you very much! Just couples of follow up questions: 1. Would "include\_in\_root" make the rows data to be effectively _stored_and _indexed_ twice? 2. I've tried disabling the "include\_in\_root" - it make the second document not to appear in the results. This is because we searching for phrase and there is not single _row_ that contains both "irland" and "setter" and since I've disabled storing all of the rows as a one document in root, it basically filters out the second document. Do I understand it right? 3. You query consist of two parts - "nested" and the second one. The first one searches through each and every row _as a single document_, and the second one searches through all of the rows _as a whole_ for each table, right? Thank you again for helping me with the first steps into the Elasticsearch. That really made a difference! Zaar > clint -- ---
**Author:** ![Clinton\_Gormley](https://avatars.discourse-cdn.com/v4/letter/c/50afbb/32.png) [@Clinton\_Gormley](https://discuss.elastic.co/u/Clinton_Gormley)\ **Post date:** [November 17, 2012, 6:10pm UTC](https://discuss.elastic.co/t/storing-table-like-data-in-elastic-search/9732/12 "2012-11-17T18:10:30Z")
Hiya > Just couples of follow up questions: > > 1. Would "include\_in\_root" make the rows data to be effectively stored > and indexed twice? Indexed twice, yes, not stored twice. But given that this is indexed as an inverted index, this doesn't add much to the index size. I ran a small test and include\_in\_root made the index 3% bigger. > 1. I've tried disabling the "include\_in\_root" - it make the second > document not to appear in the results. This is because we searching > for phrase and there is not single row that contains both "irland" and > "setter" and since I've disabled storing all of the rows as a one > document in root, it basically filters out the second document. Do I > understand it right? Correct > 1. You query consist of two parts - "nested" and the second one. The > first one searches through each and every row as a single document, > and the second one searches through all of the rows as a whole for > each table, right? yes, because when the terms are stored in the root object, they are flattened into the 'rows.row' field, instead of being stored as separate docs (as in the nested version) > Thank you again for helping me with the first steps into the > Elasticsearch. That really made a difference! glad to hear it ๐Ÿ™‚ clint -- ---
**Author:** ![phill](https://avatars.discourse-cdn.com/v4/letter/p/779978/32.png) [@phill](https://discuss.elastic.co/u/phill)\ **Post date:** [November 27, 2012, 6:33am UTC](https://discuss.elastic.co/t/storing-table-like-data-in-elastic-search/9732/13 "2012-11-27T06:33:57Z")
On 11/16/2012 7:03 AM, Clinton Gormley wrote: > I agree that the above would be ideal, but Zaar has explained that his > data has arbitrary column names, so he may end up with massive mappings. > > Zaar: your documents would be indexed like this: > > { "title" : [dogs, species], > "col\_names" : [name, description, country\_of\_origin], > "rows": [boxer, good, dog, germany, irish, setter, great, dog, irland] > } > > so you can search the "rows" field for any of those terms, and you can > use a match\_phrase query with a highish "slop" value to boost terms that > are closer together, eg "boxer good" would score higher than "boxer dog" > > clint > Wow, I never knew how an array would score for distance! I had guessed > each one term array entry would by at position 0, just like indexing two > synonyms at the same position in a stream of analyzed terms in Lucene. > Where in Lucene or Elasticsearch is it stated otherwise, so that a > phrase query would do as you say? Are possibility thinking of the case > only where all these words end up in the "all" field? -Paul -- ---
**Author:** ![Clinton\_Gormley](https://avatars.discourse-cdn.com/v4/letter/c/50afbb/32.png) [@Clinton\_Gormley](https://discuss.elastic.co/u/Clinton_Gormley)\ **Post date:** [November 27, 2012, 8:46am UTC](https://discuss.elastic.co/t/storing-table-like-data-in-elastic-search/9732/14 "2012-11-27T08:46:18Z")
> Wow, I never knew how an array would score for distance! I had guessed > each one term array entry would by at position 0, just like indexing two > synonyms at the same position in a stream of analyzed terms in Lucene. > Where in Lucene or Elasticsearch is it stated otherwise, so that a > phrase query would do as you say? Are possibility thinking of the case > only where all these words end up in the "all" field? No, not talking about the \_all field. here's a demo: > clint -- ---
**Author:** ![phill](https://avatars.discourse-cdn.com/v4/letter/p/779978/32.png) [@phill](https://discuss.elastic.co/u/phill)\ **Post date:** [November 27, 2012, 9:46am UTC](https://discuss.elastic.co/t/storing-table-like-data-in-elastic-search/9732/15 "2012-11-27T09:46:52Z")
On 11/27/2012 12:46 AM, Clinton Gormley wrote: > No, not talking about the \_all field. > here's a demo: > > [Array positions in Elasticsearch ยท GitHub](https://gist.github.com/4153193) > > clint Nice! That is good to see. I'm sure I will forget it when I need to use it, because I don't see mention in the documentation or the Java docs of any position\_offset\_gap, so never knew to even consider it. Maybe on the page. > **[Elasticsearch Platform โ€” Find real-time answers at scale](https://www.elastic.co)** > > Power insights and outcomes with the Elasticsearch Platform and AI. See into your data and find answers that matter with enterprise solutions designed to help you build, observe, and protect. Try Elasticsearch free today. The docs would need to include the default value (0?) and some understanding of how index array entries (0,1,2..) relate to analyzing a string and the Lucene offset and position information (optionally) stored about each token in a field. Apparently this attribute position\_offset\_gap refers to the word position of the term. I think in Lucene to prevent overloading the term "offset" they used the term increment (Yikes now I'm using muliple meaning of the term "term"). I also see that one of the original requests refered to the Lucene positionIncrementGap > Something to the effect: When storing offsets (see term\_vector [Elasticsearch Platform โ€” Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/reference/mapping/core-types.html)) each instance in the array of values for a field is stored with a word position _?_ (0?). You can define the difference in position between each member of an array using: position\_offset\_gap [insert clint's example here] or Igor's recent example from the thread [http://elasticsearch-users.115913.n3.nabble.com/Search-in-the-same-phrase-td4025826.html](http://elasticsearch-users.115913.n3.nabble.com/Search-in-the-same-phrase-td4025826.html) If the field is analyzed into multiple terms the position gap is from the position of the last term of the previous instance of the field to the 1st term of the next item. But the above doesn't feel clear enough. -Paul -- ---
**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:02am UTC](https://discuss.elastic.co/t/storing-table-like-data-in-elastic-search/9732/16 "2017-07-06T03:02:48Z")