JDBC static filter plugin: creating new subfield with value - getting unexpected array object

[tomj (Tom Johnson)]

I am using Logstash 7.1.1 with Elasticsearch 7.1.1

I have been able to query an external MSSQL DB and populate the internal Derby database just fine.

I'm populating the fields with the proper names, but the resultant values are not appearing as I expected.

(trying to get the markup right but apologies if I did not)

Here is a sanitized example of the original document:

{
"fields": {
      "sSID": "987656451",
      "rSID": "369829"
    },
    "@timestamp": "2019-12-10T21:57:57.182Z"
}

Here is the local_lookups section:

local_lookups => [
    {
      id => "local-rs"
      query => "select RSName from rs WHERE RSI = :rsid"
      parameters => {rsid => "[fields][rSID]"}
      target => "[fields][rSName]"
    },
    {
      id => "local-ss"
      query => "select SSName from ss WHERE SSI = :ssid"
      parameters => {ssid => "[fields][sSID]"}
      target => "[fields][sSName]"
    }
  ]

Here is an example document that shows up in Elasticsearch:

{
"fields": {
     "sSName": [
       {
         "ssname": "NAME-Customer"
       }
     ],
     "sSID": "987656451",
     "rSID": "369829",
     "rSName": [
       {
         "rsname": "NAME"
       }
     ]
   },
   "@timestamp": "2019-12-10T21:57:57.182Z"
 }

Here is what I would like to see in Elasticsearch:

{
"fields": {
      "sSName": "NAME-Customer",
      "sSID": "987656451",
      "rSID": "369829",
      "rSName": "NAME"
    },
    "@timestamp": "2019-12-10T21:57:57.182Z"
}

[Badger]

You could use mutate+rename to move them, or you could initally load them into another field, e.g.

target => "[@metadata][rSName]"

then, as described in the documentation of the target option, use an add_field option on your jdbc_static filter to copy it

add_field => { "[fields][rSName]" => "%{[@metadata][rSName][0][rsname]}" }

[tomj (Tom Johnson)]

Thank you for the tip. For some reason I read that part and reasoned it was a special case of putting fields at the root.

I will post the final solution as soon as I get it correct.

[tomj (Tom Johnson)]

It worked exactly as you suggested. Changes:

local_lookups => [ 
{ 
   id => "local-rs"
   query => "select RSName from rs WHERE RSI = :rsid" 
   parameters => {rsid => "[fields][rSID]"} 
   target => "[@metadata][rSName]" 
}, 
{ 
   id => "local-ss" 
   query => "select SSName from ss WHERE SSI = :ssid" 
   parameters => {ssid => "[fields][sSID]"} 
   target => "[@metadata][sSName]" 
} 
]

add_field => { "[fields][rSName]" => "%{[@metadata][rSName][0][rsname]}" }
add_field => { "[fields][sSName]" => "%{[@metadata][sSName][0][ssname]}" }

Thank you!