# How to mapping a json field when using logstash?

**URL:** <https://discuss.elastic.co/t/how-to-mapping-a-json-field-when-using-logstash/47618>\
**Category:** Logstash\
**Created:** [April 18, 2016, 8:14am UTC](https://discuss.elastic.co/t/how-to-mapping-a-json-field-when-using-logstash/47618 "2016-04-18T08:14:50Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![JvonRudno](https://avatars.discourse-cdn.com/v4/letter/j/258eb7/32.png) [@JvonRudno](https://discuss.elastic.co/u/JvonRudno)\
**Post date:** [April 18, 2016, 8:14am UTC](https://discuss.elastic.co/t/how-to-mapping-a-json-field-when-using-logstash/47618/1 "2016-04-18T08:14:51Z")

</div>

Hi everybody!!!

I have a postgres table that have a json field. it looks like this:

customer\_id ==\> integer  
categories ==\> json ==\> please note that this field is type json

and the table have some records like this:  
1 [{"first\_level":359,"second\_level":null}]  
2 [{"first\_level":62,"second\_level":null}]  
3 null  
4 [{"first\_level":585,"second\_level":[1559,2445]},{"first\_level":987,"second\_level":[2}]  
5 [{"first\_level":592,"second\_level":[20521]},{"first\_level":335,"second\_level":null}]

now I want to load this data in an elasticsearch index. to do this I have created a mapping like this:  
POST index\_to\_test/  
{  
"settings": {  
"number\_of\_shards": 3  
},  
"mappings": {  
"docu": {  
"properties": {  
"customer\_id": {  
"type": "integer"  
},  
"categories": {  
"type": "nested",  
"properties": {  
"firs\_level": {  
"type": "integer"  
},  
"second\_level": {  
"type": "integer"  
}  
}  
}  
}  
}  
}  
}

I will need the field categories as "nested" type, because I will need to search by separate first\_level and second\_level

the config logstash looks like this:  
input {  
jdbc {  
jdbc\_connection\_string =\> "jdbc:postgresql://dev:5432/database"  
jdbc\_user =\> "myuser"  
jdbc\_password =\> "mypassword"  
jdbc\_validate\_connection =\> true  
jdbc\_driver\_library =\> "/usr/share/elasticsearch/lib/postgresql-9.4.1208.jar"  
jdbc\_driver\_class =\> "org.postgresql.Driver"  
statement =\> "SELECT \* from table  
jdbc\_paging\_enabled =\> "true"  
jdbc\_page\_size =\> "50000"  
type =\> "docu"  
}  
}

output {  
elasticsearch{  
index =\> "index\_to\_test"  
document\_id =\> "%{customer\_id}"  
}  
}

When I execute the process to load the data, the mapping has been changed for the categories field. They has created:  
"categories": {  
"type": "nested",  
"properties": {  
"first\_level": {  
"type": "long"  
},  
"second\_level": {  
"type": "long"  
},  
"type": {  
"type": "string"  
},  
"value": {  
"type": "string"  
}  
}  
},

and the document look like this:  
"customer\_id": "1",  
"categories": {  
"type": "json",  
"value": "[{"first\_level":26,"second\_level":[342]},{"first\_level":826,"second\_level":null}]"  
},  
"@version": "1",  
"@timestamp": "2016-04-18T08:02:06.243Z",  
"type": "docu"  
}

So, may anyone tell me how I have to mapping this field in order to receive it as a nested field?

In advance thanks a lot for your suppor

Jorge von Rudno

---

<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:** [April 19, 2016, 7:46pm UTC](https://discuss.elastic.co/t/how-to-mapping-a-json-field-when-using-logstash/47618/2 "2016-04-19T19:46:25Z")

</div>

Use a json filter to parse the `[categories][value]` field.

---

<div class="post-metadata">

**Author:** ![JvonRudno](https://avatars.discourse-cdn.com/v4/letter/j/258eb7/32.png) [@JvonRudno](https://discuss.elastic.co/u/JvonRudno)\
**Post date:** [April 20, 2016, 5:56am UTC](https://discuss.elastic.co/t/how-to-mapping-a-json-field-when-using-logstash/47618/3 "2016-04-20T05:56:01Z")

</div>

Hi Magnus, Thanks a lot for your replay.  
I have reviewed the settings for the plugins filter json ([https://www.elastic.co/guide/en/logstash/current/plugins-filters-json.html](https://www.elastic.co/guide/en/logstash/current/plugins-filters-json.html)) and unfortunately I don't understand very well the way to do it. Perhaps could you be a little more specific or perhaps do you have an example to illustrate the filter.

In advance many thanks for your help.

Regards.

Jorge von Rudno

---

<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:** [April 20, 2016, 5:58am UTC](https://discuss.elastic.co/t/how-to-mapping-a-json-field-when-using-logstash/47618/4 "2016-04-20T05:58:41Z")

</div>

Example:

```auto
filter {
  json {
    source => "[categories][value]"
  }
}

```

You may want to add a `target` option to store the parsed contents under the `categories` field, and probably also a `remove_field` option to remove the `[categories][value]` field that you're probable no longer interested in.

---

<div class="post-metadata">

**Author:** ![JvonRudno](https://avatars.discourse-cdn.com/v4/letter/j/258eb7/32.png) [@JvonRudno](https://discuss.elastic.co/u/JvonRudno)\
**Post date:** [April 20, 2016, 7:30am UTC](https://discuss.elastic.co/t/how-to-mapping-a-json-field-when-using-logstash/47618/5 "2016-04-20T07:30:40Z")

</div>

Hi Magnus!!

Thanks for your quickly reply!!  
I am so sorry, but I am new in logstash and I can't understand very well how it works. Perhaps can you recommend me some documentation?.  
I am using your last post to create the filter but I am confuse because you define the source with a value, but it is dynamic for every record (document)?.  
2) if I understand well, I should parse the json object to other type. for which one? How can I do this?

Perhaps if you give me an example, it can help me a lot of!!

Thanks a lot for your patience!!

Regards

Jorge

---

<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:** [April 20, 2016, 9:30am UTC](https://discuss.elastic.co/t/how-to-mapping-a-json-field-when-using-logstash/47618/6 "2016-04-20T09:30:35Z")

</div>

> I am so sorry, but I am new in logstash and I can't understand very well how it works. Perhaps can you recommend me some documentation?.

Have you read what's available on [elastic.co](http://elastic.co)?

> I am using your last post to create the filter but I am confuse because you define the source with a value, but it is dynamic for every record (document)?.

The `source` option contains the name of the field that should be parsed. As documented, subfields are accessed via the `[field][subfield]` notation.

> if I understand well, I should parse the json object to other type. for which one? How can I do this?

I don't understand this question.

> Perhaps if you give me an example, it can help me a lot of!!

I have given you an example that I believe should work or at least be very close to what you want. Did you try it?

---

<div class="post-metadata">

**Author:** ![JvonRudno](https://avatars.discourse-cdn.com/v4/letter/j/258eb7/32.png) [@JvonRudno](https://discuss.elastic.co/u/JvonRudno)\
**Post date:** [April 25, 2016, 8:48am UTC](https://discuss.elastic.co/t/how-to-mapping-a-json-field-when-using-logstash/47618/7 "2016-04-25T08:48:53Z")

</div>

Hi magnus, please give a hand. I trying to following your suggestions, but I get an error: This is my config file:

input {  
jdbc {  
}  
}

filter {  
json {  
source =\> "[categories]"  
add\_field =\> {"{categories\_by\_level}" =\> source =\> "[categories]"}  
remove\_field =\> ["%{categories}"]  
}  
}

output {  
elasticsearch{  
index =\> "customers\_v1"  
document\_type =\> "myType"  
document\_id =\> "%{myId}"  
}  
}

The error say:  
Error: Expected one of #, {, } at line 23, column 57 (byte 700) after filter {

Thanks a lot for all your help!!!

Regards

Jorge

---

<div class="post-metadata">

**Author:** ![JvonRudno](https://avatars.discourse-cdn.com/v4/letter/j/258eb7/32.png) [@JvonRudno](https://discuss.elastic.co/u/JvonRudno)\
**Post date:** [April 25, 2016, 9:15am UTC](https://discuss.elastic.co/t/how-to-mapping-a-json-field-when-using-logstash/47618/8 "2016-04-25T09:15:19Z")

</div>

Hi Magnus, I have changed a little bit the file and it solved the last problem. Now my filter looks like this:

filter {  
json {  
source =\> "categories"  
target =\> "categories\_by\_level"  
remove\_field =\> ["categories\_by\_level"]  
}

but now I have expected in the document a field "categories\_by\_level" and I get the same field "categories" and the data still as a string not as a object (nested field)

Any suggestions?

Thanks a lot for your patience

Regards.

Jorge

---

<div class="post-metadata">

**Author:** ![JvonRudno](https://avatars.discourse-cdn.com/v4/letter/j/258eb7/32.png) [@JvonRudno](https://discuss.elastic.co/u/JvonRudno)\
**Post date:** [April 25, 2016, 10:10am UTC](https://discuss.elastic.co/t/how-to-mapping-a-json-field-when-using-logstash/47618/9 "2016-04-25T10:10:29Z")

</div>

In addition when I run logstash I get the following error message:

Error parsing json {:source=\>"categories", :raw=\>#Java::OrgPostgresqlUtil::PGobject:0x14cae44b, :exception=\>java.lang.ClassCastException: org.jruby.java.proxies.ConcreteJavaProxy cannot be cast to org.jruby.RubyIO, :level=\>:warn}

---

<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:** [April 25, 2016, 5:39pm UTC](https://discuss.elastic.co/t/how-to-mapping-a-json-field-when-using-logstash/47618/10 "2016-04-25T17:39:38Z")

</div>

> filter {  
> json {  
> source =\> "categories"  
> target =\> "categories\_by\_level"  
> remove\_field =\> ["categories\_by\_level"]  
> }

This is backwards. The field you want to remove after the filter is done is `categories`, not `categories_by_level`.

> Error parsing json {:source=\>"categories", :raw=\>#, :exception=\>java.lang.ClassCastException: org.jruby.java.proxies.ConcreteJavaProxy cannot be cast to org.jruby.RubyIO, :level=\>:warn}

I don't think I've seen this before. What does your JSON look like?

---

<div class="post-metadata">

**Author:** ![JvonRudno](https://avatars.discourse-cdn.com/v4/letter/j/258eb7/32.png) [@JvonRudno](https://discuss.elastic.co/u/JvonRudno)\
**Post date:** [April 26, 2016, 6:48am UTC](https://discuss.elastic.co/t/how-to-mapping-a-json-field-when-using-logstash/47618/11 "2016-04-26T06:48:00Z")

</div>

Good Morning Magnus,

My config file in logstash looks like this:  
`
input {
jdbc {
jdbc_connection_string => "mystringconection"
jdbc_user => "myuser"
jdbc_password => "mypassword"
jdbc_validate_connection => true
jdbc_driver_library => "mypath"
jdbc_driver_class => "org.postgresql.Driver"
statement => "SELECT * FROM customer WHERE customerno = '1'"
jdbc_paging_enabled => "true"
jdbc_page_size => "50000"
}
}`

filter {  
mutate {  
add\_field =\> {"[@metadata][index\_type]" =\> "%{index\_type}"}  
remove\_field =\> ["index\_type"]  
}

json {  
source =\> "categories\_by\_level"  
target =\> "categories\_by\_level\_json"  
remove\_field =\> ["categories\_by\_level"]  
}  
}

output {  
elasticsearch{  
index =\> "customers\_v1"  
document\_type =\> "%{[@metadata][index\_type]}"  
document\_id =\> "%{customerno}"  
}  
}

The fied categories\_by\_level that come from postgres look like this: (this field in table database is type json):

[{"first\_level":592,"second\_level":[20521]},{"first\_level":335,"second\_level":null},{"first\_level":380,"second\_level":null},{"first\_level":661,"second\_level":null},{"first\_level":391,"second\_level":[662,20277]}]

The error message when I execute logstash is:

Error parsing json {:source=\>"categories\_by\_level", :raw=\>#Java::OrgPostgresqlUtil::PGobject:0x53b3a282, :exception=\>java.lang.ClassCastException: org.jruby.java.proxies.ConcreteJavaProxy cannot be cast to org.jruby.RubyIO, :level=\>:warn}

Best regards

Jorge von Rudno

---

<div class="post-metadata">

**Author:** ![JvonRudno](https://avatars.discourse-cdn.com/v4/letter/j/258eb7/32.png) [@JvonRudno](https://discuss.elastic.co/u/JvonRudno)\
**Post date:** [May 25, 2016, 10:10am UTC](https://discuss.elastic.co/t/how-to-mapping-a-json-field-when-using-logstash/47618/12 "2016-05-25T10:10:07Z")

</div>

Hi magnusbaeck!!

In your first replay you suggest me to use a json filter to parse the [categories][value] field. I am include the filter json, some thing like this:

filter {  
json {  
source =\> "[categories][value]"  
target =\> "categories\_nested"  
remove\_field =\> ["categories"]  
}  
}

and now I get this error:  
Exception in pipelineworker, the pipeline stopped processing new events, please check your filter configuration and restart Logstash. {"exception"=\>#\<NoMethodError: undefined method `[]' for #<Java::OrgPostgresqlUtil::PGobject:0x3bc10a4>>, "backtrace"=>["/opt/logstash/vendor/bundle/jruby/1.9/gems/logstash-core-event-2.3.2-java/lib/logstash/util/accessors.rb:56:in`get'", "/opt/logstash/vendor/bundle/jruby/1.9/gems/logstash-core-event-2.3.2-java/lib/logstash/event.rb:122:in `[]'", "/opt/logstash/vendor/bundle/jruby/1.9/gems/logstash-filter-json-2.0.6/lib/logstash/filters/json.rb:69:in`filter'", "/opt/logstash/vendor/bundle/jruby/1.9/gems/logstash-core-2.3.2-java/lib/logstash/filters/base.rb:151:in `multi_filter'", "org/jruby/RubyArray.java:1613:in`each'", "/opt/logstash/vendor/bundle/jruby/1.9/gems/logstash-core-2.3.2-java/lib/logstash/filters/base.rb:148:in `multi_filter'", "(eval):41:in`filter\_func'", "/opt/logstash/vendor/bundle/jruby/1.9/gems/logstash-core-2.3.2-java/lib/logstash/pipeline.rb:267:in `filter_batch'", "org/jruby/RubyArray.java:1613:in`each'", "org/jruby/RubyEnumerable.java:852:in `inject'", "/opt/logstash/vendor/bundle/jruby/1.9/gems/logstash-core-2.3.2-java/lib/logstash/pipeline.rb:265:in`filter\_batch'", "/opt/logstash/vendor/bundle/jruby/1.9/gems/logstash-core-2.3.2-java/lib/logstash/pipeline.rb:223:in `worker_loop'", "/opt/logstash/vendor/bundle/jruby/1.9/gems/logstash-core-2.3.2-java/lib/logstash/pipeline.rb:201:in`start\_workers'"], :level=\>:error}  
NoMethodError: undefined method `[]' for #Java::OrgPostgresqlUtil::PGobject:0x3bc10a4  
get at /opt/logstash/vendor/bundle/jruby/1.9/gems/logstash-core-event-2.3.2-java/lib/logstash/util/accessors.rb:56  
[] at /opt/logstash/vendor/bundle/jruby/1.9/gems/logstash-core-event-2.3.2-java/lib/logstash/event.rb:122  
filter at /opt/logstash/vendor/bundle/jruby/1.9/gems/logstash-filter-json-2.0.6/lib/logstash/filters/json.rb:69  
multi\_filter at /opt/logstash/vendor/bundle/jruby/1.9/gems/logstash-core-2.3.2-java/lib/logstash/filters/base.rb:151  
each at org/jruby/RubyArray.java:1613  
multi\_filter at /opt/logstash/vendor/bundle/jruby/1.9/gems/logstash-core-2.3.2-java/lib/logstash/filters/base.rb:148  
filter\_func at (eval):41  
filter\_batch at /opt/logstash/vendor/bundle/jruby/1.9/gems/logstash-core-2.3.2-java/lib/logstash/pipeline.rb:267  
each at org/jruby/RubyArray.java:1613  
inject at org/jruby/RubyEnumerable.java:852  
filter\_batch at /opt/logstash/vendor/bundle/jruby/1.9/gems/logstash-core-2.3.2-java/lib/logstash/pipeline.rb:265  
worker\_loop at /opt/logstash/vendor/bundle/jruby/1.9/gems/logstash-core-2.3.2-java/lib/logstash/pipeline.rb:223  
start\_workers at /opt/logstash/vendor/bundle/jruby/1.9/gems/logstash-core-2.3.2-java/lib/logstash/pipeline.rb:201

Regards

Jorge

---

<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:56am UTC](https://discuss.elastic.co/t/how-to-mapping-a-json-field-when-using-logstash/47618/13 "2017-07-06T04:56:18Z")

</div>