# Change null value from database for one field using logstash and insert into Elasticsearch
**URL:** https://discuss.elastic.co/t/change-null-value-from-database-for-one-field-using-logstash-and-insert-into-elasticsearch/97552
**Category:** Logstash
**Created:** [August 18, 2017, 9:48am UTC](https://discuss.elastic.co/t/change-null-value-from-database-for-one-field-using-logstash-and-insert-into-elasticsearch/97552 "2017-08-18T09:48:29Z")
**Posts on this page:** 9
**Page:** 1
### Author:  [@malhotras](https://discuss.elastic.co/u/malhotras)
#### Post date: [August 18, 2017, 9:48am UTC](https://discuss.elastic.co/t/change-null-value-from-database-for-one-field-using-logstash-and-insert-into-elasticsearch/97552/1 "2017-08-18T09:48:30Z")
I would like to replace the value of field with NULL to some other
value. This field is obtained by using Logstash JDBC plugin from
database. Here is my config file.
```
input {
jdbc {
jdbc_connection_string => "url"
jdbc_user => "user"
jdbc_password => "pswd"
jdbc_driver_library => "./ifxjdbc-3-50-JC7.jar"
jdbc_driver_class => "com.informix.jdbc.IfxDriver"
statement => ["SELECT st1.name as s_name, st1.typ, st2.name as comp_name, zen.s_id, zen.comp_id, zen.conc_1, zen.conc_2 FROM sub_zen zen join sub st1 on st1.id = zen.s_id join sub st2 on st2.id = zen.comp_id"]}}
```
What I would like to do here is replace the nil value (by default typ is always nil) to P. I tried this so far.
```
filter{
mutate {
gsub => [
"typ", "nil", "P"
]
}
}
```
Does not work.
I tried this also but it throws error
```
filter{
ruby {
code => "
if event.get('typ') == nil
event.set('typ') == P
end
"
}
}
```
Can someone help here. How I can fix this.
---
### Author:  [@malhotras](https://discuss.elastic.co/u/malhotras)
#### Post date: [August 18, 2017, 9:49am UTC](https://discuss.elastic.co/t/change-null-value-from-database-for-one-field-using-logstash-and-insert-into-elasticsearch/97552/2 "2017-08-18T09:49:38Z")
@fbaligand here you go opened a new issue
---
### Author:  [@fbaligand](https://discuss.elastic.co/u/fbaligand)
#### Post date: [August 18, 2017, 1:46pm UTC](https://discuss.elastic.co/t/change-null-value-from-database-for-one-field-using-logstash-and-insert-into-elasticsearch/97552/3 "2017-08-18T13:46:55Z")
You can try this code in ruby filter :
`event.set('typ', 'P') if event.get('typ').nil?`
---
### Author:  [@malhotras](https://discuss.elastic.co/u/malhotras)
#### Post date: [August 21, 2017, 8:44am UTC](https://discuss.elastic.co/t/change-null-value-from-database-for-one-field-using-logstash-and-insert-into-elasticsearch/97552/4 "2017-08-21T08:44:10Z")
> [@fbaligand](#):
>
> event.set(‘typ’, ‘P’) if event.get(‘typ’).nil?
Thanks! But there is small correction here. Instead of single quote (') one has to use double quotes ("). Rest works great! U r great!
---
### Author:  [@fbaligand](https://discuss.elastic.co/u/fbaligand)
#### Post date: [August 21, 2017, 9:03am UTC](https://discuss.elastic.co/t/change-null-value-from-database-for-one-field-using-logstash-and-insert-into-elasticsearch/97552/5 "2017-08-21T09:03:32Z")
Very strange...
I mean :
If you use double quotes for code option (`code => " ... "`), you must use simple quotes in your code inside.
And on the contrary, if you use simple quotes for code option, you must use double quotes in your code inside.
---
### Author:  [@fbaligand](https://discuss.elastic.co/u/fbaligand)
#### Post date: [August 21, 2017, 9:09am UTC](https://discuss.elastic.co/t/change-null-value-from-database-for-one-field-using-logstash-and-insert-into-elasticsearch/97552/6 "2017-08-21T09:09:08Z")
By the way, here's a more generic way to do that :
```ruby
ruby {
code => "
event.to_hash.each do |key,value|
event.set(key, 'P') if value.nil?
end
"
}
```
---
### Author:  [@malhotras](https://discuss.elastic.co/u/malhotras)
#### Post date: [August 21, 2017, 1:00pm UTC](https://discuss.elastic.co/t/change-null-value-from-database-for-one-field-using-logstash-and-insert-into-elasticsearch/97552/7 "2017-08-21T13:00:17Z")
yes, you are right. This is how it is working at the moment. Ok, another question and probably you would ask me to open a new issue.
---
### Author:  [@malhotras](https://discuss.elastic.co/u/malhotras)
#### Post date: [August 21, 2017, 1:09pm UTC](https://discuss.elastic.co/t/change-null-value-from-database-for-one-field-using-logstash-and-insert-into-elasticsearch/97552/8 "2017-08-21T13:09:32Z")
Here is the link to my other question:
> [@Translate filter to match the custom values](https://discuss.elastic.co/t/translate-filter-to-match-the-custom-values/97773):
>
> I would like to use the translate filter dictionary plugin to define my custom values. It seems simple to use it, though is it possible to have something like this translate { dictionary =\> ["2", "f", "3", "16e", "9", "bio", "11", "wrm", "anything other than digits mentioned already", "everything else"] field =\> "format\_id" destination =\> "mformat" } So, the question is, is it possible to do something like this, where we can define everything else field?? Or, a better way …
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [September 18, 2017, 1:10pm UTC](https://discuss.elastic.co/t/change-null-value-from-database-for-one-field-using-logstash-and-insert-into-elasticsearch/97552/9 "2017-09-18T13:10:10Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.