# Filtering data from database
**URL:** https://discuss.elastic.co/t/filtering-data-from-database/34797
**Category:** Logstash
**Created:** [November 17, 2015, 12:17pm UTC](https://discuss.elastic.co/t/filtering-data-from-database/34797 "2015-11-17T12:17:38Z")
**Posts on this page:** 20
**Page:** 1
### Author:  [@vikas.saini](https://discuss.elastic.co/u/vikas.saini)
#### Post date: [November 17, 2015, 12:17pm UTC](https://discuss.elastic.co/t/filtering-data-from-database/34797/1 "2015-11-17T12:17:38Z")
I am taking data from database tables .how to filter this data ?can i filter
---
### Author:  [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)
#### Post date: [November 17, 2015, 12:20pm UTC](https://discuss.elastic.co/t/filtering-data-from-database/34797/2 "2015-11-17T12:20:04Z")
Yes.
For a more detailed answer, please ask a more detailed question. Are you using the jdbc input? What kind of filtering are you looking for?
---
### Author:  [@vikas.saini](https://discuss.elastic.co/u/vikas.saini)
#### Post date: [November 17, 2015, 12:21pm UTC](https://discuss.elastic.co/t/filtering-data-from-database/34797/3 "2015-11-17T12:21:55Z")
yes i am using jdbc input plugin
my conf file is like this
# file: contacts-index-logstash.conf
input {
jdbc {
jdbc\_connection\_string =\> "jdbc:oracle:thin:@//host:1521/orcl.newgen.co.in"
jdbc\_user =\> "od6oracle"
jdbc\_password =\> "od6oracle"
jdbc\_validate\_connection =\> true
jdbc\_driver\_library =\> "D:\logstash-2.0.0\Easy Search\ojdbc7.jar"
jdbc\_driver\_class =\> "Java::oracle.jdbc.OracleDriver"
statement =\> "select \* from pdbdocument"
}
}
filter {
mutate { add\_field =\> { "showme" =\> "This data will be in the %{author}output" } }
}
output {
elasticsearch {
index =\> "testbyme"
document\_type =\> "mine"
hosts =\> "127.0.0.1:9200"
}
}
---
### Author:  [@vikas.saini](https://discuss.elastic.co/u/vikas.saini)
#### Post date: [November 17, 2015, 12:22pm UTC](https://discuss.elastic.co/t/filtering-data-from-database/34797/4 "2015-11-17T12:22:35Z")
It indexes into elasticsearch but showme field is not there .
---
### Author:  [@vikas.saini](https://discuss.elastic.co/u/vikas.saini)
#### Post date: [November 17, 2015, 12:28pm UTC](https://discuss.elastic.co/t/filtering-data-from-database/34797/5 "2015-11-17T12:28:52Z")
Actually mysql query will fetch a table from database and that table will contain columns like address ,city,street .
by filtering these values i want to make a json object like this
"address":{
"city":{
"name":{"cityname"}
"street":{
"name":{"streetname"}
}
}
}
---
### Author:  [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)
#### Post date: [November 17, 2015, 6:24pm UTC](https://discuss.elastic.co/t/filtering-data-from-database/34797/6 "2015-11-17T18:24:07Z")
I don't know why you're not getting a `showme` field. As for the rest, use a mutate filter and its rename option to move the fields. Untested:
```
mutate {
rename => {
"city" => "[address][city][name]"
"street" => "[address][city][street]"
}
}
```
Keep in mind that if you already have a field named `address` you can't create a nested field to it. You may have to rename `address` to something else first to get it out of the way.
---
### Author:  [@vikas.saini](https://discuss.elastic.co/u/vikas.saini)
#### Post date: [November 18, 2015, 5:36am UTC](https://discuss.elastic.co/t/filtering-data-from-database/34797/7 "2015-11-18T05:36:06Z")
What you mean by that i should already have field address ?
I have address,city,street columns in my table that i fetched from database using jdbc input . and i want json like
{
"address":
{
"city":
{
"city":"cityname",
"street":"streetname"
```
}
}
```
}
---
### Author:  [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)
#### Post date: [November 18, 2015, 6:57am UTC](https://discuss.elastic.co/t/filtering-data-from-database/34797/8 "2015-11-18T06:57:49Z")
As you're saying, you already have a field called `address`. Then you can't add a nested field with that name as you have in your example, but if you first rename `address` to e.g. `address_` you can use the mutate filter in my example.
---
### Author:  [@vikas.saini](https://discuss.elastic.co/u/vikas.saini)
#### Post date: [November 18, 2015, 7:00am UTC](https://discuss.elastic.co/t/filtering-data-from-database/34797/9 "2015-11-18T07:00:55Z")
Ok but as there are two values in the city how can that be achieved .
---
### Author:  [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)
#### Post date: [November 18, 2015, 7:15am UTC](https://discuss.elastic.co/t/filtering-data-from-database/34797/10 "2015-11-18T07:15:39Z")
The mutate filter example that I posted produces what you're asking for.
I have updated it after realizing there were a few typos.
---
### Author:  [@vikas.saini](https://discuss.elastic.co/u/vikas.saini)
#### Post date: [November 18, 2015, 7:27am UTC](https://discuss.elastic.co/t/filtering-data-from-database/34797/11 "2015-11-18T07:27:25Z")
I am getting string not matched error in mutate filter ☹
[31mException in filterworker, the pipeline stopped processing new events, please
check your filter configuration and restart Logstash. {"exception"=\>#, "backtrace"=\>["org/jruby/RubyString.java:3910:in `[]='
---
### Author:  [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)
#### Post date: [November 18, 2015, 7:28am UTC](https://discuss.elastic.co/t/filtering-data-from-database/34797/12 "2015-11-18T07:28:53Z")
If you post your configuration and an example input message maybe we can help.
---
### Author:  [@vikas.saini](https://discuss.elastic.co/u/vikas.saini)
#### Post date: [November 18, 2015, 7:33am UTC](https://discuss.elastic.co/t/filtering-data-from-database/34797/13 "2015-11-18T07:33:31Z")
input {
jdbc {
jdbc\_connection\_string =\> "jdbc:oracle:thin:@//host:port/orcl.newgen.co.in"
jdbc\_user =\> "od6oracle"
jdbc\_password =\> "od6oracle"
jdbc\_validate\_connection =\> true
jdbc\_driver\_library =\> "D:\logstash-2.0.0\Easy Search\ojdbc7.jar"
jdbc\_driver\_class =\> "Java::oracle.jdbc.OracleDriver"
statement =\> "select author,name,commnt,appname
from pdbdocument"
}
}
filter {
mutate {
rename =\>{
"commnt" =\> "[name][commnt]"
"appname" =\> "[commnt][author]"
}
}
}
output {
elasticsearch { hosts =\> "localhost:9200" }
stdout { codec =\> rubydebug }
}
this is my configuration file .
---
### Author:  [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)
#### Post date: [November 18, 2015, 7:39am UTC](https://discuss.elastic.co/t/filtering-data-from-database/34797/14 "2015-11-18T07:39:40Z")
Please also post an example input message (i.e. a message emitted by the jdbc input, one that you want to filter) as requested earlier.
---
### Author:  [@vikas.saini](https://discuss.elastic.co/u/vikas.saini)
#### Post date: [November 18, 2015, 7:43am UTC](https://discuss.elastic.co/t/filtering-data-from-database/34797/15 "2015-11-18T07:43:16Z")
**author**** appname ****commnt**** name**
supervisor html bank mydoc
alice jpg no comment documnet
this is the table format output by jdbc and i want to filter it .
---
### Author:  [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)
#### Post date: [November 18, 2015, 3:07pm UTC](https://discuss.elastic.co/t/filtering-data-from-database/34797/16 "2015-11-18T15:07:01Z")
The following minimal example works and produces the output you seem to want:
```
$ cat test.config
input { stdin { codec => "json" } }
output { stdout { codec => "rubydebug" } }
filter {
mutate {
rename => {
"commnt" => "[name][commnt]"
"appname" => "[commnt][author]"
}
}
}
$ cat data
{"commnt": "this is a comment", "appname": "this is an appname"}
$ /opt/logstash/bin/logstash -f test.config < data
Logstash startup completed
{
"@version" => "1",
"@timestamp" => "2015-11-18T15:04:51.738Z",
"host" => "lnxolofon",
"name" => {
"commnt" => "this is a comment"
},
"commnt" => {
"author" => "this is an appname"
}
}
Logstash shutdown completed
```
---
### Author:  [@vikas.saini](https://discuss.elastic.co/u/vikas.saini)
#### Post date: [November 18, 2015, 4:37pm UTC](https://discuss.elastic.co/t/filtering-data-from-database/34797/17 "2015-11-18T16:37:47Z")
Thankyou so much magnus 😄 it worked !
One thing more !
I have two strings like
Str1-Name,age,country,company
Str2- myname,22,india,newgen
And i want to create json pairs like this
"Name":"myname"
"Age":22
"Country":" india"
"Company":" newgen"
And after this i already have a json like this this
Address:{
}
And i want to insert these above json pairs to insert into it
Address:{
Name":"myname"
"Age":22
"Country":" india"
"Company":" newgen"
}
Please suggest how to do this .As i can not find any loop to do this .
Please Reply as soon as possible i am stucked ☹
---
### Author:  [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)
#### Post date: [November 19, 2015, 6:49am UTC](https://discuss.elastic.co/t/filtering-data-from-database/34797/18 "2015-11-19T06:49:38Z")
Look into the [csv filter](https://www.elastic.co/guide/en/logstash/current/plugins-filters-csv.html) and use its `target` option to create the fields as subfields of `Address`.
---
### Author:  [@vikas.saini](https://discuss.elastic.co/u/vikas.saini)
#### Post date: [November 19, 2015, 10:03am UTC](https://discuss.elastic.co/t/filtering-data-from-database/34797/19 "2015-11-19T10:03:01Z")
input {
stdin {
}
}
filter {
csv {
```
columns => ["dataclassname", "age","message"]
separator => ","
}
ruby {
code => '
b = event["message"].split("#");
ary = Array.new;
for c in b;
keyvar = c;
d = "" << keyvar <<" ";
ary.push(d);
end;
event["lastColum"] = ary;
'
}
mutate
{
rename=>
{
"dataclassname" =>"[dataclass][datclassname]"
}
}
csv
{
source =>"age"
separator=>"&"
columns=>ary
target=>"[dataclass][fields]"
}
```
}
output {
elasticsearch { hosts =\> "localhost:9200" }
stdout { codec =\> rubydebug }
}
this is my conf file .here is an array i declared into ruby filter named **ary** and i am passing this array to csv filter as it takes in the column an array .but its not working .?
---
### Author:  [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)
#### Post date: [November 19, 2015, 12:58pm UTC](https://discuss.elastic.co/t/filtering-data-from-database/34797/20 "2015-11-19T12:58:40Z")
I don't understand what you're trying to do. You claim to take the results of the ruby filter and pass it to you csv filter but that's impossible since the ruby filter executes after the csv filter. Are you trying to get the csv filter to pick up the column names from an array field in the same message?
[Next page](https://discuss.elastic.co/t/filtering-data-from-database/34797.md?page=2)