# Need Help Regarding design multivalue column field in elasticsearch from river **URL:** \ **Category:** Elasticsearch\ **Created:** [October 6, 2014, 12:04pm UTC](https://discuss.elastic.co/t/need-help-regarding-design-multivalue-column-field-in-elasticsearch-from-river/20088 "2014-10-06T12:04:46Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![Phani\_Nadiminti](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/phani_nadiminti/32/45381_2.png) [@Phani\_Nadiminti](https://discuss.elastic.co/u/Phani_Nadiminti)\ **Post date:** [October 6, 2014, 12:04pm UTC](https://discuss.elastic.co/t/need-help-regarding-design-multivalue-column-field-in-elasticsearch-from-river/20088/1 "2014-10-06T12:04:46Z")
Hi All, I have a scenario like, I need to build an index with four fields (Generic Description,GenericId ,GroupNo, List of Catalogsids ). Here is sample example of mysql table. ``` Generic Description GenericId GroupNo ``` List of Catalogsids FrameKit 1 35 10,20,24,56,78,90,87 Pulley 3480 23 36 Bypass Pulley 4133 7 55 The fourth column in above table field contains multiple values for one row. Now i want to create index using above mysql table via river concept so that fourth column in the table sholud be represented as array in elastic search i tried this concept but i am not getting a good way to solve this.please help me in this. Mapping i used: ``` PUT /temp ``` { "mappings": { "tempv1" : { "properties" : { "GenericDescription" : {"type" : "string"}, "GenericId" : {"type" : "string"}, "GroupNo" : {"type" : "string"}, "descriptionids" : { "type" : "nested", "properties" : { "ListofDescriptionIDs" : {"type" : "integer"} } } } } } } Thanks in advance. phani srinivas -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/c81e61d2-c73d-42f9-82ac-86ced59f7316%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/c81e61d2-c73d-42f9-82ac-86ced59f7316%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![jprante](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jprante/32/44941_2.png) [@jprante](https://discuss.elastic.co/u/jprante)\ **Post date:** [October 6, 2014, 1:55pm UTC](https://discuss.elastic.co/t/need-help-regarding-design-multivalue-column-field-in-elasticsearch-from-river/20088/2 "2014-10-06T13:55:16Z")
The mail is hard to read, the layout is scrambled. For simple integer array, you do not need type "nested". What is the SQL statement you tried for JDBC river? You have seen the examples for bracket notation? Jörg On Mon, Oct 6, 2014 at 2:04 PM, [phani.nadiminti@goktree.com](mailto:phani.nadiminti@goktree.com) wrote: > Hi All, > > I have a scenario like, I need to build an index with four fields > (Generic Description,GenericId ,GroupNo, List of Catalogsids ). > > Here is sample example of mysql table. > > ``` > Generic Description GenericId GroupNo > > ``` > > List of Catalogsids FrameKit > 1 35 10,20,24,56,78,90,87 Pulley 3480 23 36 Bypass Pulley > 4133 7 55 > > The fourth column in above table field contains multiple values for one > row. Now i want to create index using above mysql table via river > concept so that fourth column in the table sholud be represented as array > in Elasticsearch i tried this concept but i am not getting a good way to > solve this.please help me in this. > > Mapping i used: > > ``` > PUT /temp > > ``` > > { > "mappings": { > "tempv1" : { > "properties" : { > "GenericDescription" : {"type" : "string"}, > "GenericId" : {"type" : "string"}, > "GroupNo" : {"type" : "string"}, > "descriptionids" : { > "type" : "nested", > "properties" : { > "ListofDescriptionIDs" : {"type" : "integer"} > } > } > } > } > } > } > > Thanks in advance. > phani srinivas > > -- > You received this message because you are subscribed to the Google Groups > "elasticsearch" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > To view this discussion on the web visit > [https://groups.google.com/d/msgid/elasticsearch/c81e61d2-c73d-42f9-82ac-86ced59f7316%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/c81e61d2-c73d-42f9-82ac-86ced59f7316%40googlegroups.com) > [https://groups.google.com/d/msgid/elasticsearch/c81e61d2-c73d-42f9-82ac-86ced59f7316%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/c81e61d2-c73d-42f9-82ac-86ced59f7316%40googlegroups.com?utm_medium=email&utm_source=footer) > . > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CAKdsXoGw8M0vxkrQGcWhLL\_UcRNc71t554UYr5tdYZOvs6rD-w%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAKdsXoGw8M0vxkrQGcWhLL_UcRNc71t554UYr5tdYZOvs6rD-w%40mail.gmail.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![Phani\_Nadiminti](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/phani_nadiminti/32/45381_2.png) [@Phani\_Nadiminti](https://discuss.elastic.co/u/Phani_Nadiminti)\ **Post date:** [October 7, 2014, 11:20am UTC](https://discuss.elastic.co/t/need-help-regarding-design-multivalue-column-field-in-elasticsearch-from-river/20088/3 "2014-10-07T11:20:51Z")
Hi Jorg, ``` Thanks for quick reply. I tried the bracket notation from the river ``` query itself .I tried the following query and got the result. ``` " SELECT GenericDescription,GenericId,GroupNo,ListofDescriptionIDs AS ``` `ListofDescriptionIDs[]` FROM temp" ``` it worked for me for list of descriptionIDs it is created as array. { "_index": "test", "_type": "testv1", "_id": "uYTwn2iBSKGBPt0Auz834w", "_score": 1, "_source": { "GenericDescription": "test", "GenericId": "1", "GroupNo": "35", "ListofDescriptionIDs": [ "10", "20", "61", "71", "73", "74", "77", "110", "116" ] } }, the mapping directly created by river itself in es server. the type ``` for ListofDescriptionIDs created as "string".but it is taking data as arrays from query. is this "string " is correct for the ListofDescriptionIDs field. eventhough we declared as string how ES consider as array for this field? Thanks, phani. On Monday, October 6, 2014 7:25:26 PM UTC+5:30, Jörg Prante wrote: > The mail is hard to read, the layout is scrambled. > > For simple integer array, you do not need type "nested". > > What is the SQL statement you tried for JDBC river? You have seen the > examples for bracket notation? > > Jörg > > On Mon, Oct 6, 2014 at 2:04 PM, \<[phani.n...@goktree.com](mailto:phani.n...@goktree.com) \\> > wrote: > > > Hi All, > > > > I have a scenario like, I need to build an index with four fields > > (Generic Description,GenericId ,GroupNo, List of Catalogsids ). > > > > Here is sample example of mysql table. > > > > ``` > > Generic Description GenericId GroupNo > > > > ``` > > > > List of Catalogsids FrameKit > > 1 35 10,20,24,56,78,90,87 Pulley 3480 23 36 Bypass Pulley > > > > 4133 7 55 > > > > The fourth column in above table field contains multiple values for one > > row. Now i want to create index using above mysql table via river > > concept so that fourth column in the table sholud be represented as array > > in Elasticsearch i tried this concept but i am not getting a good way to > > solve this.please help me in this. > > > > Mapping i used: > > > > ``` > > PUT /temp > > > > ``` > > > > { > > "mappings": { > > "tempv1" : { > > "properties" : { > > "GenericDescription" : {"type" : "string"}, > > "GenericId" : {"type" : "string"}, > > "GroupNo" : {"type" : "string"}, > > "descriptionids" : { > > "type" : "nested", > > "properties" : { > > "ListofDescriptionIDs" : {"type" : "integer"} > > } > > } > > } > > } > > } > > } > > > > Thanks in advance. > > phani srinivas > > > > -- > > You received this message because you are subscribed to the Google Groups > > "elasticsearch" group. > > To unsubscribe from this group and stop receiving emails from it, send an > > email to [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com) \. > > To view this discussion on the web visit > > [https://groups.google.com/d/msgid/elasticsearch/c81e61d2-c73d-42f9-82ac-86ced59f7316%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/c81e61d2-c73d-42f9-82ac-86ced59f7316%40googlegroups.com) > > [https://groups.google.com/d/msgid/elasticsearch/c81e61d2-c73d-42f9-82ac-86ced59f7316%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/c81e61d2-c73d-42f9-82ac-86ced59f7316%40googlegroups.com?utm_medium=email&utm_source=footer) > > . > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/9d1abbe3-d63d-435c-8a48-44b6bfb11144%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/9d1abbe3-d63d-435c-8a48-44b6bfb11144%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![jprante](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jprante/32/44941_2.png) [@jprante](https://discuss.elastic.co/u/jprante)\ **Post date:** [October 7, 2014, 11:35am UTC](https://discuss.elastic.co/t/need-help-regarding-design-multivalue-column-field-in-elasticsearch-from-river/20088/4 "2014-10-07T11:35:49Z")
Is the database field for "ListofDescriptionIDs" a numeric field? This would be interesting because the river should map JDBC integers to JSON integers automatically. You have the following options to enforce integer type: - create index before running the river, together with desired mappings (preferred) - declare an index type mapping in the JDBC river definition with `type_mapping` parameter Jörg On Tue, Oct 7, 2014 at 1:20 PM, [phani.nadiminti@goktree.com](mailto:phani.nadiminti@goktree.com) wrote: > Hi Jorg, > > ``` > Thanks for quick reply. I tried the bracket notation from the river > > ``` > > query itself .I tried the following query and got the result. > > ``` > " SELECT GenericDescription,GenericId,GroupNo,ListofDescriptionIDs AS > > ``` > > `ListofDescriptionIDs[]` FROM temp" > > ``` > it worked for me for list of descriptionIDs it is created as array. > > { > "_index": "test", > "_type": "testv1", > "_id": "uYTwn2iBSKGBPt0Auz834w", > "_score": 1, > "_source": { > "GenericDescription": "test", > "GenericId": "1", > "GroupNo": "35", > "ListofDescriptionIDs": [ > "10", > "20", > "61", > "71", > "73", > "74", > "77", > "110", > "116" > ] > } > }, > > the mapping directly created by river itself in es server. the type > > ``` > > for ListofDescriptionIDs created as "string".but it is taking data as > arrays from query. is this "string " is correct for the > ListofDescriptionIDs field. eventhough we declared as string how ES > consider as array for this field? > > Thanks, > phani. > > On Monday, October 6, 2014 7:25:26 PM UTC+5:30, Jörg Prante wrote: > > > The mail is hard to read, the layout is scrambled. > > > > For simple integer array, you do not need type "nested". > > > > What is the SQL statement you tried for JDBC river? You have seen the > > examples for bracket notation? > > > > Jörg > > > > On Mon, Oct 6, 2014 at 2:04 PM, [phani.n...@goktree.com](mailto:phani.n...@goktree.com) wrote: > > > > > Hi All, > > > > > > I have a scenario like, I need to build an index with four fields > > > (Generic Description,GenericId ,GroupNo, List of Catalogsids ). > > > > > > Here is sample example of mysql table. > > > > > > ``` > > > Generic Description GenericId GroupNo > > > > > > ``` > > > > > > List of Catalogsids FrameKit > > > 1 35 10,20,24,56,78,90,87 Pulley 3480 23 36 Bypass Pulley > > > > > > 4133 7 55 > > > > > > The fourth column in above table field contains multiple values for one > > > row. Now i want to create index using above mysql table via river > > > concept so that fourth column in the table sholud be represented as > > > array in Elasticsearch i tried this concept but i am not getting a good > > > way to solve this.please help me in this. > > > > > > Mapping i used: > > > > > > ``` > > > PUT /temp > > > > > > ``` > > > > > > { > > > "mappings": { > > > "tempv1" : { > > > "properties" : { > > > "GenericDescription" : {"type" : "string"}, > > > "GenericId" : {"type" : "string"}, > > > "GroupNo" : {"type" : "string"}, > > > "descriptionids" : { > > > "type" : "nested", > > > "properties" : { > > > "ListofDescriptionIDs" : {"type" : "integer"} > > > } > > > } > > > } > > > } > > > } > > > } > > > > > > Thanks in advance. > > > phani srinivas > > > > > > -- > > > You received this message because you are subscribed to the Google > > > Groups "elasticsearch" group. > > > To unsubscribe from this group and stop receiving emails from it, send > > > an email to [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com). > > > To view this discussion on the web visit [https://groups.google.com/d/](https://groups.google.com/d/) > > > msgid/elasticsearch/c81e61d2-c73d-42f9-82ac-86ced59f7316% > > > [40googlegroups.com](http://40googlegroups.com) > > > [https://groups.google.com/d/msgid/elasticsearch/c81e61d2-c73d-42f9-82ac-86ced59f7316%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/c81e61d2-c73d-42f9-82ac-86ced59f7316%40googlegroups.com?utm_medium=email&utm_source=footer) > > > . > > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). > > > > -- > > You received this message because you are subscribed to the Google Groups > > "elasticsearch" group. > > To unsubscribe from this group and stop receiving emails from it, send an > > email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > > To view this discussion on the web visit > > [https://groups.google.com/d/msgid/elasticsearch/9d1abbe3-d63d-435c-8a48-44b6bfb11144%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/9d1abbe3-d63d-435c-8a48-44b6bfb11144%40googlegroups.com) > > [https://groups.google.com/d/msgid/elasticsearch/9d1abbe3-d63d-435c-8a48-44b6bfb11144%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/9d1abbe3-d63d-435c-8a48-44b6bfb11144%40googlegroups.com?utm_medium=email&utm_source=footer) > > . > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CAKdsXoHoU%2B2Pr4y\_z2n%2Bi2AzrD3KeLs%2BY9LChMrCR%2BbJEX15AA%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAKdsXoHoU%2B2Pr4y_z2n%2Bi2AzrD3KeLs%2BY9LChMrCR%2BbJEX15AA%40mail.gmail.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![Phani\_Nadiminti](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/phani_nadiminti/32/45381_2.png) [@Phani\_Nadiminti](https://discuss.elastic.co/u/Phani_Nadiminti)\ **Post date:** [October 7, 2014, 12:10pm UTC](https://discuss.elastic.co/t/need-help-regarding-design-multivalue-column-field-in-elasticsearch-from-river/20088/5 "2014-10-07T12:10:29Z")
Hi Jorg, Thanks for the reply.The list of DescriptionIds is a string field it contains data " 10,20,24,56,78,90,87" as a string in database table for one record. The following is the mapping information for the above descriptionIds in elasticsearch. ``` { "temp_new": { "mappings": { "tempv1new": { "properties": { "GenericDescription": { "type": "string" }, "GenericId": { "type": "string" }, "GroupNo": { "type": "string" }, "ListofDescriptionIDs": { "type": "string" } } } } ``` } } ``` If listofDescriptionIds is a string please post me what are ``` our alternatives. if I create index like this(as said before ==\> array mapping) will it be searchable field. Thanks, phani On Tuesday, October 7, 2014 5:05:58 PM UTC+5:30, Jörg Prante wrote: > Is the database field for "ListofDescriptionIDs" a numeric field? This > would be interesting because the river should map JDBC integers to JSON > integers automatically. > > You have the following options to enforce integer type: > > - create index before running the river, together with desired mappings > (preferred) > > - declare an index type mapping in the JDBC river definition with > `type_mapping` parameter > > Jörg > > On Tue, Oct 7, 2014 at 1:20 PM, \<[phani.n...@goktree.com](mailto:phani.n...@goktree.com) \\> > wrote: > > > Hi Jorg, > > > > ``` > > Thanks for quick reply. I tried the bracket notation from the river > > > > ``` > > > > query itself .I tried the following query and got the result. > > > > ``` > > " SELECT GenericDescription,GenericId,GroupNo,ListofDescriptionIDs AS > > > > ``` > > > > `ListofDescriptionIDs[]` FROM temp" > > > > ``` > > it worked for me for list of descriptionIDs it is created as array. > > > > { > > "_index": "test", > > "_type": "testv1", > > "_id": "uYTwn2iBSKGBPt0Auz834w", > > "_score": 1, > > "_source": { > > "GenericDescription": "test", > > "GenericId": "1", > > "GroupNo": "35", > > "ListofDescriptionIDs": [ > > "10", > > "20", > > "61", > > "71", > > "73", > > "74", > > "77", > > "110", > > "116" > > ] > > } > > }, > > > > the mapping directly created by river itself in es server. the > > > > ``` > > > > type for ListofDescriptionIDs created as "string".but it is taking data as > > arrays from query. is this "string " is correct for the > > ListofDescriptionIDs field. eventhough we declared as string how ES > > consider as array for this field? > > > > Thanks, > > phani. > > > > On Monday, October 6, 2014 7:25:26 PM UTC+5:30, Jörg Prante wrote: > > > > > The mail is hard to read, the layout is scrambled. > > > > > > For simple integer array, you do not need type "nested". > > > > > > What is the SQL statement you tried for JDBC river? You have seen the > > > examples for bracket notation? > > > > > > Jörg > > > > > > On Mon, Oct 6, 2014 at 2:04 PM, [phani.n...@goktree.com](mailto:phani.n...@goktree.com) wrote: > > > > > > > Hi All, > > > > > > > > I have a scenario like, I need to build an index with four fields > > > > (Generic Description,GenericId ,GroupNo, List of Catalogsids ). > > > > > > > > Here is sample example of mysql table. > > > > > > > > ``` > > > > Generic Description GenericId GroupNo > > > > > > > > ``` > > > > > > > > List of Catalogsids FrameKit > > > > 1 35 10,20,24,56,78,90,87 Pulley 3480 23 36 Bypass Pulley > > > > > > > > 4133 7 55 > > > > > > > > The fourth column in above table field contains multiple values for one > > > > row. Now i want to create index using above mysql table via river > > > > concept so that fourth column in the table sholud be represented as > > > > array in Elasticsearch i tried this concept but i am not getting a good > > > > way to solve this.please help me in this. > > > > > > > > Mapping i used: > > > > > > > > ``` > > > > PUT /temp > > > > > > > > ``` > > > > > > > > { > > > > "mappings": { > > > > "tempv1" : { > > > > "properties" : { > > > > "GenericDescription" : {"type" : "string"}, > > > > "GenericId" : {"type" : "string"}, > > > > "GroupNo" : {"type" : "string"}, > > > > "descriptionids" : { > > > > "type" : "nested", > > > > "properties" : { > > > > "ListofDescriptionIDs" : {"type" : "integer"} > > > > } > > > > } > > > > } > > > > } > > > > } > > > > } > > > > > > > > Thanks in advance. > > > > phani srinivas > > > > > > > > -- > > > > You received this message because you are subscribed to the Google > > > > Groups "elasticsearch" group. > > > > To unsubscribe from this group and stop receiving emails from it, send > > > > an email to [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com). > > > > To view this discussion on the web visit [https://groups.google.com/d/](https://groups.google.com/d/) > > > > msgid/elasticsearch/c81e61d2-c73d-42f9-82ac-86ced59f7316% > > > > [40googlegroups.com](http://40googlegroups.com) > > > > [https://groups.google.com/d/msgid/elasticsearch/c81e61d2-c73d-42f9-82ac-86ced59f7316%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/c81e61d2-c73d-42f9-82ac-86ced59f7316%40googlegroups.com?utm_medium=email&utm_source=footer) > > > > . > > > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). > > > > > > -- > > > You received this message because you are subscribed to the Google Groups > > > "elasticsearch" group. > > > To unsubscribe from this group and stop receiving emails from it, send an > > > email to [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com) \. > > > To view this discussion on the web visit > > > [https://groups.google.com/d/msgid/elasticsearch/9d1abbe3-d63d-435c-8a48-44b6bfb11144%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/9d1abbe3-d63d-435c-8a48-44b6bfb11144%40googlegroups.com) > > > [https://groups.google.com/d/msgid/elasticsearch/9d1abbe3-d63d-435c-8a48-44b6bfb11144%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/9d1abbe3-d63d-435c-8a48-44b6bfb11144%40googlegroups.com?utm_medium=email&utm_source=footer) > > > . > > > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/d0b5771b-f227-43df-9fa2-9928bd7888bd%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/d0b5771b-f227-43df-9fa2-9928bd7888bd%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![jprante](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jprante/32/44941_2.png) [@jprante](https://discuss.elastic.co/u/jprante)\ **Post date:** [October 7, 2014, 12:27pm UTC](https://discuss.elastic.co/t/need-help-regarding-design-multivalue-column-field-in-elasticsearch-from-river/20088/6 "2014-10-07T12:27:42Z")
Yes, in the mapping, the JDBC river automatically sets field type "string". This is because the bracket notation mode does not examine the exploded values for types like integer but simply assumes "string". "string" is ok for search. If you want numeric values with type "integer" you have to define the mapping for field "ListofDescriptionIDs" manually before the river auto-creates it. ES will then coerce the values into "integer". Jörg On Tue, Oct 7, 2014 at 2:10 PM, [phani.nadiminti@goktree.com](mailto:phani.nadiminti@goktree.com) wrote: > Hi Jorg, > > Thanks for the reply.The list of DescriptionIds is a string field it > contains data " 10,20,24,56,78,90,87" as a string in database table for one > record. > > The following is the mapping information for the above descriptionIds > in elasticsearch. > > ``` > { > "temp_new": { > "mappings": { > "tempv1new": { > "properties": { > "GenericDescription": { > "type": "string" > }, > "GenericId": { > "type": "string" > }, > "GroupNo": { > "type": "string" > }, > "ListofDescriptionIDs": { > "type": "string" > } > } > } > } > > ``` > > } > } > > ``` > If listofDescriptionIds is a string please post me what > > ``` > > are our alternatives. if I create index like this(as said before ==\> array > mapping) will it be searchable field. > Thanks, > > phani > > On Tuesday, October 7, 2014 5:05:58 PM UTC+5:30, Jörg Prante wrote: > > > Is the database field for "ListofDescriptionIDs" a numeric field? This > > would be interesting because the river should map JDBC integers to JSON > > integers automatically. > > > > You have the following options to enforce integer type: > > > > - create index before running the river, together with desired mappings > > (preferred) > > > > - declare an index type mapping in the JDBC river definition with > > `type_mapping` parameter > > > > Jörg > > > > On Tue, Oct 7, 2014 at 1:20 PM, [phani.n...@goktree.com](mailto:phani.n...@goktree.com) wrote: > > > > > Hi Jorg, > > > > > > ``` > > > Thanks for quick reply. I tried the bracket notation from the river > > > > > > ``` > > > > > > query itself .I tried the following query and got the result. > > > > > > ``` > > > " SELECT GenericDescription,GenericId,GroupNo,ListofDescriptionIDs > > > > > > ``` > > > > > > AS `ListofDescriptionIDs[]` FROM temp" > > > > > > ``` > > > it worked for me for list of descriptionIDs it is created as array. > > > > > > { > > > "_index": "test", > > > "_type": "testv1", > > > "_id": "uYTwn2iBSKGBPt0Auz834w", > > > "_score": 1, > > > "_source": { > > > "GenericDescription": "test", > > > "GenericId": "1", > > > "GroupNo": "35", > > > "ListofDescriptionIDs": [ > > > "10", > > > "20", > > > "61", > > > "71", > > > "73", > > > "74", > > > "77", > > > "110", > > > "116" > > > ] > > > } > > > }, > > > > > > the mapping directly created by river itself in es server. the > > > > > > ``` > > > > > > type for ListofDescriptionIDs created as "string".but it is taking data as > > > arrays from query. is this "string " is correct for the > > > ListofDescriptionIDs field. eventhough we declared as string how ES > > > consider as array for this field? > > > > > > Thanks, > > > phani. > > > > > > On Monday, October 6, 2014 7:25:26 PM UTC+5:30, Jörg Prante wrote: > > > > > > > The mail is hard to read, the layout is scrambled. > > > > > > > > For simple integer array, you do not need type "nested". > > > > > > > > What is the SQL statement you tried for JDBC river? You have seen the > > > > examples for bracket notation? > > > > > > > > Jörg > > > > > > > > On Mon, Oct 6, 2014 at 2:04 PM, [phani.n...@goktree.com](mailto:phani.n...@goktree.com) wrote: > > > > > > > > > Hi All, > > > > > > > > > > I have a scenario like, I need to build an index with four fields > > > > > (Generic Description,GenericId ,GroupNo, List of Catalogsids ). > > > > > > > > > > Here is sample example of mysql table. > > > > > > > > > > ``` > > > > > Generic Description GenericId GroupNo > > > > > > > > > > ``` > > > > > > > > > > List of Catalogsids FrameKit > > > > > 1 35 10,20,24,56,78,90,87 Pulley 3480 23 36 Bypass Pulley > > > > > > > > > > 4133 7 55 > > > > > > > > > > The fourth column in above table field contains multiple values for > > > > > one row. Now i want to create index using above mysql table via river > > > > > concept so that fourth column in the table sholud be represented as > > > > > array in Elasticsearch i tried this concept but i am not getting a good > > > > > way to solve this.please help me in this. > > > > > > > > > > Mapping i used: > > > > > > > > > > ``` > > > > > PUT /temp > > > > > > > > > > ``` > > > > > > > > > > { > > > > > "mappings": { > > > > > "tempv1" : { > > > > > "properties" : { > > > > > "GenericDescription" : {"type" : "string"}, > > > > > "GenericId" : {"type" : "string"}, > > > > > "GroupNo" : {"type" : "string"}, > > > > > "descriptionids" : { > > > > > "type" : "nested", > > > > > "properties" : { > > > > > "ListofDescriptionIDs" : {"type" : "integer"} > > > > > } > > > > > } > > > > > } > > > > > } > > > > > } > > > > > } > > > > > > > > > > Thanks in advance. > > > > > phani srinivas > > > > > > > > > > -- > > > > > You received this message because you are subscribed to the Google > > > > > Groups "elasticsearch" group. > > > > > To unsubscribe from this group and stop receiving emails from it, send > > > > > an email to [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com). > > > > > To view this discussion on the web visit [https://groups.google.com/d/](https://groups.google.com/d/) > > > > > msgid/elasticsearch/c81e61d2-c73d-42f9-82ac-86ced59f7316%40goo > > > > > [glegroups.com](http://glegroups.com) > > > > > [https://groups.google.com/d/msgid/elasticsearch/c81e61d2-c73d-42f9-82ac-86ced59f7316%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/c81e61d2-c73d-42f9-82ac-86ced59f7316%40googlegroups.com?utm_medium=email&utm_source=footer) > > > > > . > > > > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). > > > > > > > > -- > > > > You received this message because you are subscribed to the Google > > > > Groups "elasticsearch" group. > > > > To unsubscribe from this group and stop receiving emails from it, send > > > > an email to [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com). > > > > To view this discussion on the web visit [https://groups.google.com/d/](https://groups.google.com/d/) > > > > msgid/elasticsearch/9d1abbe3-d63d-435c-8a48-44b6bfb11144% > > > > [40googlegroups.com](http://40googlegroups.com) > > > > [https://groups.google.com/d/msgid/elasticsearch/9d1abbe3-d63d-435c-8a48-44b6bfb11144%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/9d1abbe3-d63d-435c-8a48-44b6bfb11144%40googlegroups.com?utm_medium=email&utm_source=footer) > > > > . > > > > > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). > > > > -- > > You received this message because you are subscribed to the Google Groups > > "elasticsearch" group. > > To unsubscribe from this group and stop receiving emails from it, send an > > email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > > To view this discussion on the web visit > > [https://groups.google.com/d/msgid/elasticsearch/d0b5771b-f227-43df-9fa2-9928bd7888bd%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/d0b5771b-f227-43df-9fa2-9928bd7888bd%40googlegroups.com) > > [https://groups.google.com/d/msgid/elasticsearch/d0b5771b-f227-43df-9fa2-9928bd7888bd%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/d0b5771b-f227-43df-9fa2-9928bd7888bd%40googlegroups.com?utm_medium=email&utm_source=footer) > > . > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CAKdsXoGwdUyC3c2ZswNBBc-y%3DjVXuyK2Sft4BDTANoMZyCctng%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAKdsXoGwdUyC3c2ZswNBBc-y%3DjVXuyK2Sft4BDTANoMZyCctng%40mail.gmail.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![Phani\_Nadiminti](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/phani_nadiminti/32/45381_2.png) [@Phani\_Nadiminti](https://discuss.elastic.co/u/Phani_Nadiminti)\ **Post date:** [October 8, 2014, 3:09pm UTC](https://discuss.elastic.co/t/need-help-regarding-design-multivalue-column-field-in-elasticsearch-from-river/20088/7 "2014-10-08T15:09:19Z")
Hi Jorg, I believe that there is difference between normal string mapping and dynamic array mapping using bracket notation in Elasticsearch . If we do normal string type for "ListofDescriptionIds" in Elasticsearch where i can find exact difference in searching data in dynamic array and normal string field in Elasticsearch. Dynamic array of type string in elasticsearch. "ListofDescriptionIDs": [ "10", "20", "61", "71", "73", "74", "77", "110", "116" ] Normal string notation: "ListofDescriptionIDs": "10,20,61,71,73,74,77,110,116" Thanks phani On Tuesday, October 7, 2014 5:58:02 PM UTC+5:30, Jörg Prante wrote: > Yes, in the mapping, the JDBC river automatically sets field type > "string". This is because the bracket notation mode does not examine the > exploded values for types like integer but simply assumes "string". > "string" is ok for search. > > If you want numeric values with type "integer" you have to define the > mapping for field "ListofDescriptionIDs" manually before the river > auto-creates it. ES will then coerce the values into "integer". > > Jörg > > On Tue, Oct 7, 2014 at 2:10 PM, \<[phani.n...@goktree.com](mailto:phani.n...@goktree.com) \\> > wrote: > > > Hi Jorg, > > > > Thanks for the reply.The list of DescriptionIds is a string field it > > contains data " 10,20,24,56,78,90,87" as a string in database table for one > > record. > > > > The following is the mapping information for the above descriptionIds > > in elasticsearch. > > > > ``` > > { > > "temp_new": { > > "mappings": { > > "tempv1new": { > > "properties": { > > "GenericDescription": { > > "type": "string" > > }, > > "GenericId": { > > "type": "string" > > }, > > "GroupNo": { > > "type": "string" > > }, > > "ListofDescriptionIDs": { > > "type": "string" > > } > > } > > } > > } > > > > ``` > > > > } > > } > > > > ``` > > If listofDescriptionIds is a string please post me what > > > > ``` > > > > are our alternatives. if I create index like this(as said before ==\> array > > mapping) will it be searchable field. > > Thanks, > > > > phani > > > > On Tuesday, October 7, 2014 5:05:58 PM UTC+5:30, Jörg Prante wrote: > > > > > Is the database field for "ListofDescriptionIDs" a numeric field? This > > > would be interesting because the river should map JDBC integers to JSON > > > integers automatically. > > > > > > You have the following options to enforce integer type: > > > > > > - create index before running the river, together with desired mappings > > > (preferred) > > > > > > - declare an index type mapping in the JDBC river definition with > > > `type_mapping` parameter > > > > > > Jörg > > > > > > On Tue, Oct 7, 2014 at 1:20 PM, [phani.n...@goktree.com](mailto:phani.n...@goktree.com) wrote: > > > > > > > Hi Jorg, > > > > > > > > ``` > > > > Thanks for quick reply. I tried the bracket notation from the river > > > > > > > > ``` > > > > > > > > query itself .I tried the following query and got the result. > > > > > > > > ``` > > > > " SELECT GenericDescription,GenericId,GroupNo,ListofDescriptionIDs > > > > > > > > ``` > > > > > > > > AS `ListofDescriptionIDs[]` FROM temp" > > > > > > > > ``` > > > > it worked for me for list of descriptionIDs it is created as array. > > > > > > > > { > > > > "_index": "test", > > > > "_type": "testv1", > > > > "_id": "uYTwn2iBSKGBPt0Auz834w", > > > > "_score": 1, > > > > "_source": { > > > > "GenericDescription": "test", > > > > "GenericId": "1", > > > > "GroupNo": "35", > > > > "ListofDescriptionIDs": [ > > > > "10", > > > > "20", > > > > "61", > > > > "71", > > > > "73", > > > > "74", > > > > "77", > > > > "110", > > > > "116" > > > > ] > > > > } > > > > }, > > > > > > > > the mapping directly created by river itself in es server. the > > > > > > > > ``` > > > > > > > > type for ListofDescriptionIDs created as "string".but it is taking data as > > > > arrays from query. is this "string " is correct for the > > > > ListofDescriptionIDs field. eventhough we declared as string how ES > > > > consider as array for this field? > > > > > > > > Thanks, > > > > phani. > > > > > > > > On Monday, October 6, 2014 7:25:26 PM UTC+5:30, Jörg Prante wrote: > > > > > > > > > The mail is hard to read, the layout is scrambled. > > > > > > > > > > For simple integer array, you do not need type "nested". > > > > > > > > > > What is the SQL statement you tried for JDBC river? You have seen the > > > > > examples for bracket notation? > > > > > > > > > > Jörg > > > > > > > > > > On Mon, Oct 6, 2014 at 2:04 PM, [phani.n...@goktree.com](mailto:phani.n...@goktree.com) wrote: > > > > > > > > > > > Hi All, > > > > > > > > > > > > I have a scenario like, I need to build an index with four fields > > > > > > (Generic Description,GenericId ,GroupNo, List of Catalogsids ). > > > > > > > > > > > > Here is sample example of mysql table. > > > > > > > > > > > > ``` > > > > > > Generic Description GenericId GroupNo > > > > > > > > > > > > ``` > > > > > > > > > > > > List of Catalogsids FrameKit > > > > > > 1 35 10,20,24,56,78,90,87 Pulley 3480 23 36 Bypass Pulley > > > > > > > > > > > > 4133 7 55 > > > > > > > > > > > > The fourth column in above table field contains multiple values for > > > > > > one row. Now i want to create index using above mysql table via river > > > > > > concept so that fourth column in the table sholud be represented as > > > > > > array in Elasticsearch i tried this concept but i am not getting a good > > > > > > way to solve this.please help me in this. > > > > > > > > > > > > Mapping i used: > > > > > > > > > > > > ``` > > > > > > PUT /temp > > > > > > > > > > > > ``` > > > > > > > > > > > > { > > > > > > "mappings": { > > > > > > "tempv1" : { > > > > > > "properties" : { > > > > > > "GenericDescription" : {"type" : "string"}, > > > > > > "GenericId" : {"type" : "string"}, > > > > > > "GroupNo" : {"type" : "string"}, > > > > > > "descriptionids" : { > > > > > > "type" : "nested", > > > > > > "properties" : { > > > > > > "ListofDescriptionIDs" : {"type" : "integer"} > > > > > > } > > > > > > } > > > > > > } > > > > > > } > > > > > > } > > > > > > } > > > > > > > > > > > > Thanks in advance. > > > > > > phani srinivas > > > > > > > > > > > > -- > > > > > > You received this message because you are subscribed to the Google > > > > > > Groups "elasticsearch" group. > > > > > > To unsubscribe from this group and stop receiving emails from it, > > > > > > send an email to [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com). > > > > > > To view this discussion on the web visit [https://groups.google.com/d/](https://groups.google.com/d/) > > > > > > msgid/elasticsearch/c81e61d2-c73d-42f9-82ac-86ced59f7316%40goo > > > > > > [glegroups.com](http://glegroups.com) > > > > > > [https://groups.google.com/d/msgid/elasticsearch/c81e61d2-c73d-42f9-82ac-86ced59f7316%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/c81e61d2-c73d-42f9-82ac-86ced59f7316%40googlegroups.com?utm_medium=email&utm_source=footer) > > > > > > . > > > > > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). > > > > > > > > > > -- > > > > > You received this message because you are subscribed to the Google > > > > > Groups "elasticsearch" group. > > > > > To unsubscribe from this group and stop receiving emails from it, send > > > > > an email to [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com). > > > > > To view this discussion on the web visit [https://groups.google.com/d/](https://groups.google.com/d/) > > > > > msgid/elasticsearch/9d1abbe3-d63d-435c-8a48-44b6bfb11144% > > > > > [40googlegroups.com](http://40googlegroups.com) > > > > > [https://groups.google.com/d/msgid/elasticsearch/9d1abbe3-d63d-435c-8a48-44b6bfb11144%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/9d1abbe3-d63d-435c-8a48-44b6bfb11144%40googlegroups.com?utm_medium=email&utm_source=footer) > > > > > . > > > > > > > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). > > > > > > -- > > > You received this message because you are subscribed to the Google Groups > > > "elasticsearch" group. > > > To unsubscribe from this group and stop receiving emails from it, send an > > > email to [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com) \. > > > To view this discussion on the web visit > > > [https://groups.google.com/d/msgid/elasticsearch/d0b5771b-f227-43df-9fa2-9928bd7888bd%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/d0b5771b-f227-43df-9fa2-9928bd7888bd%40googlegroups.com) > > > [https://groups.google.com/d/msgid/elasticsearch/d0b5771b-f227-43df-9fa2-9928bd7888bd%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/d0b5771b-f227-43df-9fa2-9928bd7888bd%40googlegroups.com?utm_medium=email&utm_source=footer) > > > . > > > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/4761615e-5221-47f9-b5b6-a9aa686b038a%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/4761615e-5221-47f9-b5b6-a9aa686b038a%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**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, 12:57am UTC](https://discuss.elastic.co/t/need-help-regarding-design-multivalue-column-field-in-elasticsearch-from-river/20088/8 "2017-07-06T00:57:35Z")