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
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
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
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.
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
Apache, Apache Lucene, Apache Hadoop, Hadoop, HDFS and the yellow elephant
logo are trademarks of the
Apache Software Foundation
in the United States and/or other countries.