# Can't access inner fields in JSON **URL:** \ **Category:** Logstash\ **Created:** [December 31, 2020, 6:39pm UTC](https://discuss.elastic.co/t/cant-access-inner-fields-in-json/259978 "2020-12-31T18:39:09Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![ake](https://avatars.discourse-cdn.com/v4/letter/a/ee59a6/32.png) [@ake](https://discuss.elastic.co/u/ake)\ **Post date:** [December 31, 2020, 6:39pm UTC](https://discuss.elastic.co/t/cant-access-inner-fields-in-json/259978/1 "2020-12-31T18:39:09Z")
Hi everyone, I'm trying to extract some fields from JSON that I'm getting from MySQL and add it to ES. I've been going over way too many discussions here and some other sites but can't find the solution for my problem. As you can see I have a JSON field in my db table called `detail_json` and from it I need to extract information out. basically I want to flatten that JSON by extracting some fields like for example: brand, model, model\_variant and title". I've tried: `add_field => {"title" =>"%{[json][title]}"}` but that just gives me this string: `"%{[json][title]}"` I also tried using the ruby to get it from the event but I'm getting `"tags":["_rubyexception"]` and some other things that I've seen online but nothing works. So, please help me if you can. here's my mapping: ```auto mapping = { "mappings": { "properties": { "vehicles": { "type": "nested", "properties": { "brand": {"type": "text", "analyzer": "standard"}, "model": {"type": "text", "analyzer": "standard"}, "model_variant": {"type": "text", "analyzer": "standard"}, } }, "title": {"type": "text", "analyzer": "standard", "fields": { "raw": {"type": "keyword"} }}, ..... ``` Here's my pipeline: ```auto input { jdbc { jdbc_driver_library => "/usr/share/java/mysql-connector-java-8.0.22.jar" jdbc_driver_class => "com.mysql.jdbc.Driver" jdbc_connection_string => "jdbc:mysql://IP:3306/DB_NAME" jdbc_user => "usename" jdbc_password => "pass" jdbc_paging_enabled => true schedule => "*/5 * * * * *" statement => "SELECT id, detail_json FROM vehicle_details" } } filter { json { source => "detail_json" target => "json" } ruby { init => "require 'json'" code => " title = event.get('[detail_json][title]') #event.set('title', title) " } mutate { add_field => {"title" =>"%{[json][title]}"} copy => { "id" => "[@metadata][_id]" } remove_field => ["id", "@version"] } } output { stdout { codec => "json_lines"} elasticsearch { hosts => ["IP:9200"] index => "vehicle_details" document_id => "%{[@metadata][_id]}" } } ``` The `detail_json` in the db looks like: ```auto { "title": "title2", "vehicles": [ { "brand": "Mercedes", "model": "A-Class", "model_variant": "A 200", "year_from": 2020 }, { "brand": "Mercedes", "model": "A-Class", "model_variant": "A 160", "year_from": 2020 } ] } ``` I tried this first but was getting `null` for every single field that is in `detail_json` for some reason. The `id` works just fine: `SELECT id, detail_json->'$.vehicles' AS vehicles FROM vehicle_details` ---
**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\ **Post date:** [December 31, 2020, 7:19pm UTC](https://discuss.elastic.co/t/cant-access-inner-fields-in-json/259978/2 "2020-12-31T19:19:25Z")
What does an event look like on stdout? ---
**Author:** ![ake](https://avatars.discourse-cdn.com/v4/letter/a/ee59a6/32.png) [@ake](https://discuss.elastic.co/u/ake)\ **Post date:** [December 31, 2020, 7:31pm UTC](https://discuss.elastic.co/t/cant-access-inner-fields-in-json/259978/3 "2020-12-31T19:31:31Z")
it doesn't. I'm seeing `"tags":["_rubyexception"]` ---
**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\ **Post date:** [December 31, 2020, 8:25pm UTC](https://discuss.elastic.co/t/cant-access-inner-fields-in-json/259978/4 "2020-12-31T20:25:10Z")
> [@ake](#): > > ` stdout { codec => "json_lines"}` Does that not produce output? ---
**Author:** ![ake](https://avatars.discourse-cdn.com/v4/letter/a/ee59a6/32.png) [@ake](https://discuss.elastic.co/u/ake)\ **Post date:** [December 31, 2020, 10:03pm UTC](https://discuss.elastic.co/t/cant-access-inner-fields-in-json/259978/5 "2020-12-31T22:03:36Z")
oh sorry: ```auto [INFO] 2020-12-31 18:15:45.255 [Ruby-0-Thread-26: :1] jdbc - (0.001469s) SELECT * FROM (SELECT id, detail_json FROM solution) AS `t1` LIMIT 100000 OFFSET 0 [ERROR] 2020-12-31 18:15:45.359 [[main]>worker2] ruby - Ruby exception occurred: no implicit conversion of nil into String [ERROR] 2020-12-31 18:15:45.359 [[main]>worker0] ruby - Ruby exception occurred: no implicit conversion of nil into String {"json":"{\"vehicles\": [{\"brand\": \"Mercedes\", \"model\": \"E-Class\", \"model_variant\": \"E220\", \"year_from\": 2000, \"year_to\": 2005}], \"offer\": [{\"offer_type\": \"Standard\", \"price\": 50}, {\"offer_type\": \"Premium\", \"price\": 60, \"additional_package\": [\"option1\"], \"assistance_minutes\": 30}], \"fuel_type\": \"Diesel\", \"transmission\": \"Automatic\", \"issue_type_option\": \"Cockpit module\", \"title\": \"Solution for cockpit module crackling\", \"short_description\": \"Solution for strange crackling noise coming from cockpit module.\", \"long_description\": \"Solution for strange crackling noise coming from cockpit module when shifting gears\", \"note\": \"There are pictures and video clips for the solution!\", \"keywords\": [\"Mercedes\"], \"visibility\": true}","detail_json":"\"{\\\"vehicles\\\": [{\\\"brand\\\": \\\"Mercedes\\\", \\\"model\\\": \\\"E-Class\\\", \\\"model_variant\\\": \\\"E220\\\", \\\"year_from\\\": 2000, \\\"year_to\\\": 2005}], \\\"offer\\\": [{\\\"offer_type\\\": \\\"Standard\\\", \\\"price\\\": 50}, {\\\"offer_type\\\": \\\"Premium\\\", \\\"price\\\": 60, \\\"additional_package\\\": [\\\"option1\\\"], \\\"assistance_minutes\\\": 30}], \\\"fuel_type\\\": \\\"Diesel\\\", \\\"transmission\\\": \\\"Automatic\\\", \\\"issue_type_option\\\": \\\"Cockpit module\\\", \\\"title\\\": \\\"Solution for cockpit module crackling\\\", \\\"short_description\\\": \\\"Solution for strange crackling noise coming from cockpit module.\\\", \\\"long_description\\\": \\\"Solution for strange crackling noise coming from cockpit module when shifting gears\\\", \\\"note\\\": \\\"There are pictures and video clips for the solution!\\\", \\\"keywords\\\": [\\\"Mercedes\\\"], \\\"visibility\\\": true}\"","tags":["_rubyexception"],"@timestamp":"2020-12-31T18:15:45.257Z"} {"json":"{\"vehicles\": [{\"brand\": \"Mercedes\", \"model\": \"A-Class\", \"model_variant\": \"A 160\", \"year_from\": 2000, \"year_to\": 2005}], \"offer\": [{\"offer_type\": \"Standard\", \"price\": 30}, {\"offer_type\": \"Premium\", \"price\": 40, \"additional_package\": [\"option1\"], \"assistance_minutes\": 22}], \"fuel_type\": \"Petrol\", \"transmission\": \"Manual\", \"issue_type_option\": \"Cockpit module\", \"title\": \"Solution for cockpit module\", \"short_description\": \"Solution for strange noise coming from cockpit module.\", \"long_description\": \"Solution for strange noise coming from cockpit module when shifting gears\", \"note\": \"There are pictures and video clips for the solution!\", \"keywords\": [\"Mercedes\", \"AMG\"], \"visibility\": true}","detail_json":"\"{\\\"vehicles\\\": [{\\\"brand\\\": \\\"Mercedes\\\", \\\"model\\\": \\\"A-Class\\\", \\\"model_variant\\\": \\\"A 160\\\", \\\"year_from\\\": 2000, \\\"year_to\\\": 2005}], \\\"offer\\\": [{\\\"offer_type\\\": \\\"Standard\\\", \\\"price\\\": 30}, {\\\"offer_type\\\": \\\"Premium\\\", \\\"price\\\": 40, \\\"additional_package\\\": [\\\"option1\\\"], \\\"assistance_minutes\\\": 22}], \\\"fuel_type\\\": \\\"Petrol\\\", \\\"transmission\\\": \\\"Manual\\\", \\\"issue_type_option\\\": \\\"Cockpit module\\\", \\\"title\\\": \\\"Solution for cockpit module\\\", \\\"short_description\\\": \\\"Solution for strange noise coming from cockpit module.\\\", \\\"long_description\\\": \\\"Solution for strange noise coming from cockpit module when shifting gears\\\", \\\"note\\\": \\\"There are pictures and video clips for the solution!\\\", \\\"keywords\\\": [\\\"Mercedes\\\", \\\"AMG\\\"], \\\"visibility\\\": true}\"","tags":["_rubyexception"],"@timestamp":"2020-12-31T18:15:45.256Z"} ``` It's saying that `[json][title]` is nil for some reason. the same thing is when I do `SELECT id, detail_json->'$.vehicles' AS vehicles FROM vehicle_details`. Is it some weird JSON parsing happening or something as the fields are in the JSON but can't extract it ---
**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\ **Post date:** [December 31, 2020, 10:16pm UTC](https://discuss.elastic.co/t/cant-access-inner-fields-in-json/259978/6 "2020-12-31T22:16:55Z")
> [@ake](#): > > ```auto > json { > source => "detail_json" > target => "json" > } > > ``` You need a second json filter after that ``` json { source => "json" } ``` After that you will have a [title] field at the top level. And you can remove this ``` add_field => {"title" =>"%{[json][title]}"} ``` ---
**Author:** ![ake](https://avatars.discourse-cdn.com/v4/letter/a/ee59a6/32.png) [@ake](https://discuss.elastic.co/u/ake)\ **Post date:** [December 31, 2020, 10:34pm UTC](https://discuss.elastic.co/t/cant-access-inner-fields-in-json/259978/7 "2020-12-31T22:34:50Z")
That is it, thank you very much and happy new year 🙂 ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [January 28, 2021, 10:34pm UTC](https://discuss.elastic.co/t/cant-access-inner-fields-in-json/259978/8 "2021-01-28T22:34:51Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.