Append an object (nested) from Logstash to Elasticsearch

Elastic Stack Logstash
1

Hello Elasticusers,

I have a little issue, I have a the following configuration in Logstash :

  1. input : jdbc to execute a SQL query
  2. filter : mutate to rename the fields of the SQL query and to create some objects
  3. output : elasticsearch to upsert the document into Elasticsearch

This is my configuration's file :

input {
	jdbc {
		jdbc_driver_library => "path_to_my_connector"
		jdbc_driver_class => "com.mysql.jdbc.Driver"
		jdbc_connection_string => "connection_to_my_db"
		jdbc_user => "user"
		jdbc_password => "password"
		statement => "SELECT 
						film.film_id as id,
						film.title,
						film.description,
						film.release_year,
						film.length,
						film.special_features,
						actor.actor_id as actor_id,
						actor.first_name as actor_first_name,
						actor.last_name as actor_last_name
					FROM
						actor,
						film,
						film_actor,
					WHERE
						film_actor.film_id = film.film_id
					AND
						actor.actor_id = film_actor.actor_id
					AND
						film.film_id = 1;"
	}
}
filter {
	mutate {
		remove_field => ["host","@timestamp","@version","path","message"]
		rename =>{
			"actor_id" => "[actor][id]"
			"actor_first_name" => "[actor][first_name]"
			"actor_last_name" => "[actor][last_name]"
		}
	}
}
output { 
	elasticsearch {
		hosts => ["elastic_host"]
		index => "my_index"
		document_type => "my_type"
		document_id => "%{id}"
		action => "update"
		doc_as_upsert => true
		script_lang => "groovy"
		script_type => "inline"
		script => "I don't find the good script"
	}

}

My issue : I don't find the good script in order to add dynamicaly the object [actor] if it's not already present in the document.

For information : in elasticsearch this is the mapping of my nested object "actor" :

"actor": {
"properties": {
"id": {
"type": "long"
},
"first_name": {
"index": "not_analyzed",
"type": "string"
},
"last_name": {
"index": "not_analyzed",
"type": "string"
}
},
"type": "nested"
}

Thank you in advande for your helps.

1 Like
2

Having the same issue where in my case, each row in MYSQL can have multiple objects, but they're inserted as array of strings instead of field from object.
I'm using grok in my case instead of mutate:

     grok {
        match => ["AFIELD", "\A%{TIME:[nestedObjName][start]} --> %{TIME:[nestedObjName][end]}%{GREEDYDATA:[nestedObjName][sentence]}" ]
      }

but instead of having

nestedObjName : [
    {
        start: '---',
        end : '---',
        sentence: '---'
        },
        {
            start: '---',
            end : '---',
            sentence: '---'
            }
    ]

im getting :

"NotNestedObjName": {
"sentence": [
"---",
"---"
],
"start": [
"---",
"---"
],
"end": [
"---",
"---"
]
}

3

were you able to resolve it ?