# Setting a filter that transforms "NULL" values to a default value from Mysql to elasticSearch

**URL:** <https://discuss.elastic.co/t/setting-a-filter-that-transforms-null-values-to-a-default-value-from-mysql-to-elasticsearch/59642>\
**Category:** Logstash\
**Created:** [September 2, 2016, 8:37am UTC](https://discuss.elastic.co/t/setting-a-filter-that-transforms-null-values-to-a-default-value-from-mysql-to-elasticsearch/59642 "2016-09-02T08:37:34Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Sadok\_Mtir](https://avatars.discourse-cdn.com/v4/letter/s/258eb7/32.png) [@Sadok\_Mtir](https://discuss.elastic.co/u/Sadok_Mtir)\
**Post date:** [September 2, 2016, 8:37am UTC](https://discuss.elastic.co/t/setting-a-filter-that-transforms-null-values-to-a-default-value-from-mysql-to-elasticsearch/59642/1 "2016-09-02T08:37:34Z")

</div>

The fields that I want to get them with default value could be NULL in MYSQL. This is my configuration for the logstash plugin.

```
input {
    jdbc {
        jdbc_connection_string => "jdbc:mysql://localhost:3306/elements"
        jdbc_user => "user"
        jdbc_password => "admin"
        jdbc_validate_connection => true
        jdbc_driver_library => "C:/work/Wildfly/wildfly-9.0.2.Final/modules/com/mysql/main/mysql-connector-java-5.1.36.jar"
        jdbc_driver_class => "com.mysql.jdbc.Driver"
        statement_filepath => "query.sql"
        use_column_value => true
        tracking_column => id
        #schedule => "*/3 * * * *"
        clean_run => true

    }
}

output {
    elasticsearch {
        index => "emptytest"
        document_type => "history"
        document_id => "%{id}"
        hosts => "localhost"
    }

```

I tried this filter but the condition does not detect the NULL values.

```
if [sourcecell_id] == "NULL" {

         mutate {

         }
    }
```

---

<div class="post-metadata">

**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\
**Post date:** [September 4, 2016, 7:52pm UTC](https://discuss.elastic.co/t/setting-a-filter-that-transforms-null-values-to-a-default-value-from-mysql-to-elasticsearch/59642/2 "2016-09-04T19:52:14Z")

</div>

I'm not sure Logstash handles null values very gracefully. Can't you transform the null values in the SQL query instead?

---

<div class="post-metadata">

**Author:** ![Sadok\_Mtir](https://avatars.discourse-cdn.com/v4/letter/s/258eb7/32.png) [@Sadok\_Mtir](https://discuss.elastic.co/u/Sadok_Mtir)\
**Post date:** [September 5, 2016, 8:16am UTC](https://discuss.elastic.co/t/setting-a-filter-that-transforms-null-values-to-a-default-value-from-mysql-to-elasticsearch/59642/3 "2016-09-05T08:16:13Z")

</div>

I can but that would be the last solution. The farthest thing that I did is via a ruby script is to delete the null columns but I want to substitute the null with default value like 0 for example.  
This is the script that I found:

```
 filter {
     ruby {
                code => "
                        hash = event.to_hash
                        hash.each do |k,v|
                                if v == nil
                                        event.remove(k)
                                end
                        end
                "
        }

}
```

---

<div class="post-metadata">

**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\
**Post date:** [September 5, 2016, 8:29am UTC](https://discuss.elastic.co/t/setting-a-filter-that-transforms-null-values-to-a-default-value-from-mysql-to-elasticsearch/59642/4 "2016-09-05T08:29:35Z")

</div>

Yes, a ruby filter would do. Replace `event.remove(k)` with `event[k] = 0`.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [July 6, 2017, 4:39am UTC](https://discuss.elastic.co/t/setting-a-filter-that-transforms-null-values-to-a-default-value-from-mysql-to-elasticsearch/59642/5 "2017-07-06T04:39:56Z")

</div>