JDBC River Multiple nested objects

Kalyan_Srinivas · April 4, 2014, 8:38pm

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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/09900f90-341d-4cf5-b058-f737a5a60807%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Kalyan_Srinivas · April 4, 2014, 8:42pm

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 embd.name
http://embd.name 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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/4dc82a3c-43b1-47f1-b3c9-035d4bc30dc6%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Kalyan_Srinivas · April 4, 2014, 8:51pm

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 embd.name
http://embd.name 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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/b31d2fa1-979a-4d1c-bf28-950012fb2722%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

jprante · April 5, 2014, 10:50am

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.comwrote:

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 embd.name
http://embd.name 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.
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?utm_medium=email&utm_source=footer
.

For more options, visit 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.
To view this discussion on the web visit 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.