Map nested field into DB output using Elastic input plugin

[jasoninmel (Jason)]

Hi there,

I'm using Logstash as a ETL tool to copy data from Elastic index to DB table. I tried to map a nested field using following insert statement in the logstash configuration;

statement => ["INSERT into assets (id, title, productid) values (?,?,?)", "id", "title", "[products][productId]"]

productId is a nested field within products object.

The insert statement I have used doesn't work as the productId column in the DB does not get populated while the other fields are added.

Appreciate if you could assist.

Thanks,
Jason

[Badger]

@jasoninmel

"id", "title", "[products][productId]"

Try

"id", "title", "%{[products][productId]}"

[jasoninmel (Jason)]

That did not work unfortunately. Unlike previously where other data fields had data added, with what you have proposed, none of the fields in the database table is populated.

[Badger]

I am assuming that you are using the community jdbc output plugin. That is not supported by Elastic and the author no longer maintains it, so there is nobody you can go to for support.

My reading of the code that adds parameters to the statement is that either version of your statement option should work. I do not know what to suggest.

[jasoninmel (Jason)]

Thanks @Badger. I installed logstash using YUM using the steps given in https://www.elastic.co/guide/en/logstash/current/installing-logstash.html#_yum

Then used the following command to install jdbc plugin and the version I got is logstash-output-jdbc (5.4.0).
plugin install logstash-output-jdbc

I also installed Java SE 8 and postgres jdbc driver as I'm using Postgres DB.

[jasoninmel (Jason)]

I get the following error when I try to access the nested field using "%{[products][productId]}".

jdbc - JDBC - Exception. Not retrying {:exception=>#<RuntimeError: Invalid FieldReference: %{[products][productId]}>

However I do not see any errors when using "[products][productId]".

It is difficult to debug when no errors are shown.

In Elasticsearch document structure products is an array of product objects which would appear as follows;
{ "description": "This is a test product", "products": [ { "productId": "23232", "country": "AU" } ] }

Since there can be more than one productId if multiple products exist within products object array it wouldn't be possible to match the correct field. Could this be the reason for not being able to capture the nested field productId ?

[jasoninmel (Jason)]

Given the products is an array of objects, I changed the field mapping in JDBC statement to use first element of the array as follows;

"id", "title", "[products][0][productId]"

This indeed fixed the problem. It was my mistake not referring to the correct element in the array.