Can't access inner fields in JSON

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:

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:

    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:

{
  "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

What does an event look like on stdout?

it doesn't. I'm seeing "tags":["_rubyexception"]

Does that not produce output?

oh sorry:

[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

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]}"}

That is it, thank you very much and happy new year :slight_smile: