Hi everyone,
I'm trying to extract some fields from JSON that I'm getting from MySQL and add it to ES.
I've been going over way too many discussions here and some other sites but can't find the solution for my problem.
As you can see I have a JSON field in my db table called detail_json and from it I need to extract information out. basically I want to flatten that JSON by extracting some fields like for example: brand, model, model_variant and title".
I've tried: add_field => {"title" =>"%{[json][title]}"} but that just gives me this string: "%{[json][title]}"
I also tried using the ruby to get it from the event but I'm getting "tags":["_rubyexception"] and some other things that I've seen online but nothing works. So, please help me if you can.
here's my mapping:
mapping = {
"mappings": {
"properties": {
"vehicles": {
"type": "nested",
"properties": {
"brand": {"type": "text", "analyzer": "standard"},
"model": {"type": "text", "analyzer": "standard"},
"model_variant": {"type": "text", "analyzer": "standard"},
}
},
"title": {"type": "text",
"analyzer": "standard",
"fields": {
"raw": {"type": "keyword"}
}},
.....
Here's my pipeline:
input {
jdbc {
jdbc_driver_library => "/usr/share/java/mysql-connector-java-8.0.22.jar"
jdbc_driver_class => "com.mysql.jdbc.Driver"
jdbc_connection_string => "jdbc:mysql://IP:3306/DB_NAME"
jdbc_user => "usename"
jdbc_password => "pass"
jdbc_paging_enabled => true
schedule => "*/5 * * * * *"
statement => "SELECT id, detail_json FROM vehicle_details"
}
}
filter {
json {
source => "detail_json"
target => "json"
}
ruby {
init => "require 'json'"
code => "
title = event.get('[detail_json][title]')
#event.set('title', title)
"
}
mutate {
add_field => {"title" =>"%{[json][title]}"}
copy => { "id" => "[@metadata][_id]" }
remove_field => ["id", "@version"]
}
}
output {
stdout { codec => "json_lines"}
elasticsearch {
hosts => ["IP:9200"]
index => "vehicle_details"
document_id => "%{[@metadata][_id]}"
}
}
The detail_json in the db looks like:
{
"title": "title2",
"vehicles": [
{
"brand": "Mercedes",
"model": "A-Class",
"model_variant": "A 200",
"year_from": 2020
},
{
"brand": "Mercedes",
"model": "A-Class",
"model_variant": "A 160",
"year_from": 2020
}
]
}
I tried this first but was getting null for every single field that is in detail_json for some reason. The id works just fine:
SELECT id, detail_json->'$.vehicles' AS vehicles FROM vehicle_details
