Hello,
I have 3 tables:
- users
- posts
- comments
I made a left join query to get users then posts then comments like this:
SELECT users.id as user_id, users.name as user_name, posts.id as post_id, posts.title as post_title, posts.user_id as post_user_id, comments.id as comment_id, comments.post_id as comment_post_id, comments.comment as comment_title FROM users LEFT JOIN posts on users.id = posts.user_id LEFT JOIN comments on posts.id = comments.post_id
And i made aggregation the goal from it to have document like this
{
user_id : 1,
user_name: jake
posts: [
{
post_id: 1
comments: [
{
comment_id: 2
}
{comment_id: 3
}
]
}
]
}
And this is my aggregation:
filter {
mutate {
rename => {
"post_id" => "[posts][post_id]"
"post_title" => "[posts][post_title]"
"post_user_id" => "[posts][post_user_id]"
"comment_id" => "[posts][comments][comment_id]"
"comment_title" => "[posts][comments][comment_title]"
"comment_post_id" => "[posts][comments][comment_post_id]"
}
}
aggregate {
task_id => "%{user_id}"
code => "
map['posts'] ||= []
event.to_hash.each do |key,value|
map[key] = value unless map.has_key?(key)
map[key] << value if map[key].is_a?(Array)
end
"
push_previous_map_as_event => true
}
aggregate {
task_id => "%{post_id}"
code => "
map['posts']['comments'] ||= [{}]
event.to_hash.each do |key,value|
map[key] = value unless map.has_key?(key)
map[key] << value if map[key].is_a?(Array)
end
"
push_previous_map_as_event => true
}
}
But i got a repeated post documents as the number of related comments for the same post
Do you have any suggestion or solution for this issue ?
Thanks