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

**URL:** <https://discuss.elastic.co/t/jdbc-static-filter-plugin-creating-new-subfield-with-value-getting-unexpected-array-object/211385>\
**Category:** Logstash\
**Created:** [December 10, 2019, 10:38pm UTC](https://discuss.elastic.co/t/jdbc-static-filter-plugin-creating-new-subfield-with-value-getting-unexpected-array-object/211385 "2019-12-10T22:38:49Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![tomj](https://avatars.discourse-cdn.com/v4/letter/t/dfb087/32.png) [@tomj](https://discuss.elastic.co/u/tomj)\
**Post date:** [December 10, 2019, 10:38pm UTC](https://discuss.elastic.co/t/jdbc-static-filter-plugin-creating-new-subfield-with-value-getting-unexpected-array-object/211385/1 "2019-12-10T22:38:49Z")

</div>

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

---

<div class="post-metadata">

**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 11, 2019, 12:09am UTC](https://discuss.elastic.co/t/jdbc-static-filter-plugin-creating-new-subfield-with-value-getting-unexpected-array-object/211385/2 "2019-12-11T00:09:57Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![tomj](https://avatars.discourse-cdn.com/v4/letter/t/dfb087/32.png) [@tomj](https://discuss.elastic.co/u/tomj)\
**Post date:** [December 11, 2019, 1:03am UTC](https://discuss.elastic.co/t/jdbc-static-filter-plugin-creating-new-subfield-with-value-getting-unexpected-array-object/211385/3 "2019-12-11T01:03:34Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![tomj](https://avatars.discourse-cdn.com/v4/letter/t/dfb087/32.png) [@tomj](https://discuss.elastic.co/u/tomj)\
**Post date:** [December 12, 2019, 3:46pm UTC](https://discuss.elastic.co/t/jdbc-static-filter-plugin-creating-new-subfield-with-value-getting-unexpected-array-object/211385/4 "2019-12-12T15:46:46Z")

</div>

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!

---

<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:** [January 9, 2020, 3:46pm UTC](https://discuss.elastic.co/t/jdbc-static-filter-plugin-creating-new-subfield-with-value-getting-unexpected-array-object/211385/5 "2020-01-09T15:46:55Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.