# JDBC River Multiple nested objects **URL:** \ **Category:** Elasticsearch\ **Created:** [April 4, 2014, 8:38pm UTC](https://discuss.elastic.co/t/jdbc-river-multiple-nested-objects/16827 "2014-04-04T20:38:50Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![Kalyan\_Srinivas](https://avatars.discourse-cdn.com/v4/letter/k/779978/32.png) [@Kalyan\_Srinivas](https://discuss.elastic.co/u/Kalyan_Srinivas)\ **Post date:** [April 4, 2014, 8:38pm UTC](https://discuss.elastic.co/t/jdbc-river-multiple-nested-objects/16827/1 "2014-04-04T20:38:50Z")
HI All I am new to elasticsearch and i am trying to index data from ms sql server,i am struggling with constructing structured object, i have 3 tables blogTable, blogassociation, blogimages each lead blog may have multiple images and each blog may may multiple embedded blogs so i want ES document which looks like this { "blogname":title, "datepublished":"2014-03-12", "association":[ { "blogname":title, "datepublished":"2014-03-12" },.....repeat block ], "attachments":[ { "leadblogimagecode":1235, "leadblogimagepath":"path\_to\_image" },repeat block] } so my sql query looks like this select id as \_id,name as "lead.blogname",lead.datepublished as "datepublished",embd.blogname as "association[blogname]",datepublished as "association[datepublished]",img.id as "attachment[leadblogimagecode]",img.path as "attachment[leadblogimagepath]" from blogtable lead inner join blogassociation embd on embd.id = lead.id inner join blogimages img on img.id = lead.id result of the above query looks like this \*LeadID Blogname datepublisged embd.id embd.name img.id img.path \* 4679 Tesla, Abe and Elba 2014-01-06 00:00:00 3917 Idris Elba 9450 /web/q/g/h/57436356.jpg 4679 Tesla, Abe and Elba 2014-01-06 00:00:00 3917 Idris Elba 9965 /web/i/s/q/GS3193626.jpg 4679 Tesla, Abe and Elba 2014-01-06 00:00:00 3917 Idris Elba 9451 /web/i/s/q/GS3193626.jpg -- 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/09900f90-341d-4cf5-b058-f737a5a60807%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/09900f90-341d-4cf5-b058-f737a5a60807%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![Kalyan\_Srinivas](https://avatars.discourse-cdn.com/v4/letter/k/779978/32.png) [@Kalyan\_Srinivas](https://discuss.elastic.co/u/Kalyan_Srinivas)\ **Post date:** [April 4, 2014, 8:42pm UTC](https://discuss.elastic.co/t/jdbc-river-multiple-nested-objects/16827/2 "2014-04-04T20:42:21Z")
sorry accidentally poste before completeing the question so contd my result looks like this LeadID Blogname datepublisged association[id] association[name] attachment[id] attachment[path] 4679 Tesla, Abe and Elba 2014-01-06 00:00:00 3917 Idris Elba 9450 /web/q/g/h/57436356.jpg 4679 Tesla, Abe and Elba 2014-01-06 00:00:00 3917 Idris Elba 9965 /web/i/s/q/GS3193626.jpg 4679 Tesla, Abe and Elba 2014-01-06 00:00:00 3917 Idris Elba 9451 /web/i/s/q/GS3193626.jpg On Friday, April 4, 2014 1:38:50 PM UTC-7, Kalyan Srinivas wrote: > HI All > I am new to elasticsearch and i am trying to index data from ms sql > server,i am struggling with constructing structured object, > > i have 3 tables blogTable, blogassociation, blogimages > each lead blog may have multiple images and each blog may may multiple > embedded blogs > so i want ES document which looks like this > { > "blogname":title, > "datepublished":"2014-03-12", > "association":[ > { "blogname":title, > "datepublished":"2014-03-12" > },.....repeat block ], > "attachments":[ > { "leadblogimagecode":1235, > "leadblogimagepath":"path\_to\_image" },repeat block] > } > > so my sql query looks like this > select id as \_id,name as "lead.blogname",lead.datepublished as > "datepublished",embd.blogname as "association[blogname]",datepublished as > "association[datepublished]",img.id as > "attachment[leadblogimagecode]",img.path as "attachment[leadblogimagepath]" > from blogtable lead inner join blogassociation embd on embd.id = lead.idinner join blogimages img on > img.id = lead.id > > result of the above query looks like this > \*LeadID Blogname datepublisged embd.id [http://embd.id](http://embd.id) embd.name > [http://embd.name](http://embd.name) img.id [http://img.id](http://img.id) img.path \* > 4679 Tesla, Abe and Elba 2014-01-06 00:00:00 3917 Idris Elba 9450 > /web/q/g/h/57436356.jpg > 4679 Tesla, Abe and Elba 2014-01-06 00:00:00 3917 Idris Elba 9965 > /web/i/s/q/GS3193626.jpg > 4679 Tesla, Abe and Elba 2014-01-06 00:00:00 3917 Idris Elba 9451 > /web/i/s/q/GS3193626.jpg -- 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/4dc82a3c-43b1-47f1-b3c9-035d4bc30dc6%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/4dc82a3c-43b1-47f1-b3c9-035d4bc30dc6%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![Kalyan\_Srinivas](https://avatars.discourse-cdn.com/v4/letter/k/779978/32.png) [@Kalyan\_Srinivas](https://discuss.elastic.co/u/Kalyan_Srinivas)\ **Post date:** [April 4, 2014, 8:51pm UTC](https://discuss.elastic.co/t/jdbc-river-multiple-nested-objects/16827/3 "2014-04-04T20:51:01Z")
so the problem i am facing is i get duplicates in my association and my attachment ,so its basically it's a multiple of # of rows in blogassociation and blogimages.so considering the above example i get 3 duplicates in association I hope i am making sense with the question,please help me or direct me if i am doing something wrong On Friday, April 4, 2014 1:38:50 PM UTC-7, Kalyan Srinivas wrote: > HI All > I am new to elasticsearch and i am trying to index data from ms sql > server,i am struggling with constructing structured object, > > i have 3 tables blogTable, blogassociation, blogimages > each lead blog may have multiple images and each blog may may multiple > embedded blogs > so i want ES document which looks like this > { > "blogname":title, > "datepublished":"2014-03-12", > "association":[ > { "blogname":title, > "datepublished":"2014-03-12" > },.....repeat block ], > "attachments":[ > { "leadblogimagecode":1235, > "leadblogimagepath":"path\_to\_image" },repeat block] > } > > so my sql query looks like this > select id as \_id,name as "lead.blogname",lead.datepublished as > "datepublished",embd.blogname as "association[blogname]",datepublished as > "association[datepublished]",img.id as > "attachment[leadblogimagecode]",img.path as "attachment[leadblogimagepath]" > from blogtable lead inner join blogassociation embd on embd.id = lead.idinner join blogimages img on > img.id = lead.id > > result of the above query looks like this > \*LeadID Blogname datepublisged embd.id [http://embd.id](http://embd.id) embd.name > [http://embd.name](http://embd.name) img.id [http://img.id](http://img.id) img.path \* > 4679 Tesla, Abe and Elba 2014-01-06 00:00:00 3917 Idris Elba 9450 > /web/q/g/h/57436356.jpg > 4679 Tesla, Abe and Elba 2014-01-06 00:00:00 3917 Idris Elba 9965 > /web/i/s/q/GS3193626.jpg > 4679 Tesla, Abe and Elba 2014-01-06 00:00:00 3917 Idris Elba 9451 > /web/i/s/q/GS3193626.jpg -- 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/b31d2fa1-979a-4d1c-bf28-950012fb2722%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/b31d2fa1-979a-4d1c-bf28-950012fb2722%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:** [April 5, 2014, 10:50am UTC](https://discuss.elastic.co/t/jdbc-river-multiple-nested-objects/16827/4 "2014-04-05T10:50:59Z")
Yes, that is because the JSON doc generator in JDBC river can not collapse JSON object entries in an array into one if they are equal. In the end, it does not harm in the index (ES would accept 3 objects with same information) but displaying it is odd. The column name defs I would use look like "\_id", "blog.name", "blog.published", "blog.association[id]", "blog.association[name]", "blog.attachment[id]", "blog.attachment[name]" but maybe there are some hackers out there who want to patch JDBC river for better JSON tree construction directives from tabular data? Jörg On Fri, Apr 4, 2014 at 10:51 PM, Kalyan Srinivas [kalyan.kuram@gmail.com](mailto:kalyan.kuram@gmail.com)wrote: > so the problem i am facing is i get duplicates in my association and my > attachment ,so its basically it's a multiple of # of rows in > blogassociation and blogimages.so considering the above example i get 3 > duplicates in association > > I hope i am making sense with the question,please help me or direct me if > i am doing something wrong > > On Friday, April 4, 2014 1:38:50 PM UTC-7, Kalyan Srinivas wrote: > > > HI All > > I am new to elasticsearch and i am trying to index data from ms sql > > server,i am struggling with constructing structured object, > > > > i have 3 tables blogTable, blogassociation, blogimages > > each lead blog may have multiple images and each blog may may multiple > > embedded blogs > > so i want ES document which looks like this > > { > > "blogname":title, > > "datepublished":"2014-03-12", > > "association":[ > > { "blogname":title, > > "datepublished":"2014-03-12" > > },.....repeat block ], > > "attachments":[ > > { "leadblogimagecode":1235, > > "leadblogimagepath":"path\_to\_image" },repeat > > block] > > } > > > > so my sql query looks like this > > select id as \_id,name as "lead.blogname",lead.datepublished as > > "datepublished",embd.blogname as "association[blogname]",datepublished > > as "association[datepublished]",img.id as "attachment[leadblogimagecode]",img.path > > as "attachment[leadblogimagepath]" from blogtable lead inner join > > blogassociation embd on embd.id = lead.id inner join blogimages img on > > img.id = lead.id > > > > result of the above query looks like this > > \*LeadID Blogname datepublisged embd.id [http://embd.id](http://embd.id) embd.name > > [http://embd.name](http://embd.name) img.id [http://img.id](http://img.id) img.path \* > > 4679 Tesla, Abe and Elba 2014-01-06 00:00:00 3917 Idris Elba 9450 > > /web/q/g/h/57436356.jpg > > 4679 Tesla, Abe and Elba 2014-01-06 00:00:00 3917 Idris Elba 9965 > > /web/i/s/q/GS3193626.jpg > > 4679 Tesla, Abe and Elba 2014-01-06 00:00:00 3917 Idris Elba 9451 > > /web/i/s/q/GS3193626.jpg > > -- > 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/b31d2fa1-979a-4d1c-bf28-950012fb2722%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/b31d2fa1-979a-4d1c-bf28-950012fb2722%40googlegroups.com)[https://groups.google.com/d/msgid/elasticsearch/b31d2fa1-979a-4d1c-bf28-950012fb2722%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/b31d2fa1-979a-4d1c-bf28-950012fb2722%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/CAKdsXoG605xGTU4wCp-RKKb%3DiyE%3DHLU\_-EtUA4CwwUENQ9xfGA%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAKdsXoG605xGTU4wCp-RKKb%3DiyE%3DHLU_-EtUA4CwwUENQ9xfGA%40mail.gmail.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, 1:38am UTC](https://discuss.elastic.co/t/jdbc-river-multiple-nested-objects/16827/5 "2017-07-06T01:38:00Z")