I have a posts table that contains some blog posts. Each post has some tags that are stored in some other table.
For example, for post with id 1, that has 5 tags, the post_tags table has the following entries:
tagId, postId
1, 1
2, 1
3, 1
4, 1
5, 1
The tagId is related to a tag name that can be fethced by doing a join with the tag table
tag table schema:
id, name
post table schema:
id,
title,
likes,
comments,
published,
userId
Now, I want to create a single index that has the following fields:
postId,
title,
likes,
comments,
published,
userId,
tags
(id => postId and tags field)
The tags field should be an array of tag names.
How can I achieve this with jdbc_driver?
Here's what I have tried:
input {
jdbc {
jdbc_connection_string => "SKIPPED"
jdbc_user => "SKIPPED"
jdbc_password => "SKIPPED"
jdbc_driver_library => "SKIPPED"
jdbc_driver_class => "SKIPPED"
statement => "SELECT post.id as postId, post.likes, post.comments, post.userId, post.title, post.published from post where published > :sql_last_value"
type => "posts"
lowercase_column_names => false
}
jdbc {
jdbc_connection_string => "SKIPPED"
jdbc_user => "SKIPPED"
jdbc_password => "SKIPPED"
jdbc_driver_library => "SKIPPED"
jdbc_driver_class => "SKIPPED"
statement => "select postId, GROUP_CONCAT(tag.name) as posttags from post_tags, tag, post WHERE post.id = post_tags.postId AND tag.id = post_tags.tagId AND post.published > :sql_last_value GROUP BY post_tags.postId"
lowercase_column_names => false
type => "posttags"
}
}
filter {
aggregate {
task_id => "%{postId}"
code => "
if (event.get('type') == 'posttags')
map['posttags'] ||= event.get('posttags').split(',')
event.cancel()
end
if(event.get('type') == 'posts')
map['postId'] = event.get('postId')
map['likes'] = event.get('likes')
map['comments'] = event.get('comments')
map['userId'] = event.get('userId')
map['title'] = event.get('title')
map['published'] = event.get('published')
end
"
push_previous_map_as_event => true
timeout => 300
}
}
output {
stdout { codec => rubydebug }
elasticsearch {
document_id => "%{postId}"
hosts => "localhost"
index => "posts"
}
}
Or am I just better off writing a script for this?