How to configure logstash file to make nested object inside another nested filed from sql query?

[My-project-repositor]

I am new in Elasticsearch and logstash and I have an issue with nested objects. So what I want to ask is how can I create index with multiple nested fields inside another nested fields.

How can I build the data inside logstash file configuration from data from postgres to index something like below inside elasticsearch?

For example, I have a Product which have raw materials and raw materials have substances

product {
 id: 1,
 name: "name",
 raw_materials: [{
  id: 1,
  name: "raw_name",
  substances: [{
    id: 1,
    name: "sub_name",
  }]
 }]
}

Help me please

[dadoonet (David Pilato)]

I don't think there's an easy way for doing such a thing.

But I'd read Jdbc input plugin | Logstash Reference [7.12] | Elastic

That's being said, I shared most of my thoughts there: http://david.pilato.fr/blog/2015/05/09/advanced-search-for-your-legacy-application/

Basically, I'd recommend modifying the application layer if possible and send data to elasticsearch in the same "transaction" as you are sending your data to the database instead of trying to read that later and try to compose complex objects (this has a significant cost on the source database).

Have also a look at this "live coding" recording.

[My-project-repositor]

Thanks for your answer

We already have a lot of data in the current database so sending data in ES with the same transaction in the database may not be possible.

Do you think that flatten the data without nesting all the fields would be approprite to perform search?