Logstash document id string does not get evaluated

To prevent data duplication while injesting data from logstash I am adding a document_id string for logstash conf with a peopleRowId column. However it does not get evaluated. So in my case I am trying to set document id as document_id => "%{[document][projectsRowId]}" , However this does not get evaluated for some reason and id in Elasticsearch as

        "_index" : "projectsv3",
        "_type" : "_doc",
        "_id" : "%{[document][projectsRowId]}",
        "_score" : 1.0,
        "_source" : {...single record}


I am not sure why document id is not getting enabled. Using Elasticsearch 7 and ECS is also disabled. I have tried out other methods too like filter with fingerprint I have also tried to set document id as document_id => "%{projectsRowId}" although in all cases it does not get evaluated so there is just one document with _id as %{projectsRowId}". If I exclude document_id there are duplicates which get created.

input {
    jdbc {
        jdbc_driver_library => "C:\\ElasticStack\\mysql-connector-java-8.0.24\\mysql-connector-java-8.0.24.jar"
        jdbc_driver_class => "com.mysql.jdbc.Driver"
        # mysql jdbc connection string to our database, mydb
         jdbc_connection_string =>  "jdbc:mysql://"
        # The user we wish to execute our statement as
        jdbc_user => "root"
        jdbc_password => "root"
        schedule => "* * * * *"
        clean_run => true
        # use_column_value => true
        # tracking_column => "%{[@metadata][fingerprint]}"
        # tracking_column_type => "numeric"
        # our query to fetch people details
        statement => "select ROW_NUMBER() OVER (
        ORDER BY a.created_at
    ) as projectsRowId , (a.created_at), tr.report_number as 'tech_report_number', tr.file_s3 as 'tech_report_file_name', tr.abstract as 'tech_report_abstract' , c.prefix as 'piPrefix' , c.first_name as 'piFirstName', c.middle_name as 'piMiddleName' ,c.last_name as 'piLastName', b.person_id, d.prefix as 'coPiPrefix' "
        # use_column_value => true
        # tracking_column => id
        # tracking_column_type => "numeric"
output {
   elasticsearch {
       action => "create"
       hosts => ""
       index => "projectsv3"
       doc_as_upsert => true
       document_id => "%{[document][projectsRowId]}"

Set lowercase_column_names to false and use "%{[projectsRowId]}" in the document_id option.

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.