# Insert complex nested json documents from Postgres to Elasticsearch via Logstash

**URL:** <https://discuss.elastic.co/t/insert-complex-nested-json-documents-from-postgres-to-elasticsearch-via-logstash/185813>\
**Category:** Logstash\
**Created:** [June 14, 2019, 9:06am UTC](https://discuss.elastic.co/t/insert-complex-nested-json-documents-from-postgres-to-elasticsearch-via-logstash/185813 "2019-06-14T09:06:34Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![MARCO\_RAMBALDI](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/marco_rambaldi/32/46108_2.png) [@MARCO\_RAMBALDI](https://discuss.elastic.co/u/MARCO_RAMBALDI)\
**Post date:** [June 14, 2019, 9:06am UTC](https://discuss.elastic.co/t/insert-complex-nested-json-documents-from-postgres-to-elasticsearch-via-logstash/185813/1 "2019-06-14T09:06:34Z")

</div>

Hi,  
I have seen in other topics and I have also verified in first person that Logstash does not recognize json object from the jdbc input and an error related to the PGobject comes out, like this:

Exception when executing JDBC query {:exception=\>#\<Sequel::DatabaseError: Java::OrgLogstash::MissingConverterException: Missing Converter handling for full class name=org.postgresql.util.PGobject, simple name=PGobject\>}

So I tried to work around the problem by casting the json object in text format, so i have this configuration pipeline:

# file.conf

input {  
jdbc {  
# Postgres jdbc connection string to my database  
# The user we wish to execute our statement as  
# The path to my downloaded jdbc driver  
# The name of the driver class for Postgresql  
# password  
# my query  
statement =\> "SELECT document::text from snapshots"  
schedule =\> "\*\*\*\*"  
}  
}

filter{  
json{  
source =\> "document"  
remove\_field =\> ["document"]  
}  
}

output {  
stdout { codec =\> json\_lines }  
elasticsearch {  
index =\> "snapshots"  
document\_id =\> "%{uid}"  
hosts =\> ["localhost"]  
}  
}

The pipeline runs, but I noticed that on kibana I can only see the last json document that is fished from the query as if the others were overwritten.

I would like to have on kibana all the documents that are saved in postgres within the document column.  
The structure of the json is very complex and nested, in fact there are about 450 fields.  
How can I solve this problem?

---

<div class="post-metadata">

**Author:** ![Divit\_Sharma](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/divit_sharma/32/32348_2.png) [@Divit\_Sharma](https://discuss.elastic.co/u/Divit_Sharma)\
**Post date:** [June 14, 2019, 12:33pm UTC](https://discuss.elastic.co/t/insert-complex-nested-json-documents-from-postgres-to-elasticsearch-via-logstash/185813/2 "2019-06-14T12:33:15Z")

</div>

Is uid a primary key?

---

<div class="post-metadata">

**Author:** ![MARCO\_RAMBALDI](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/marco_rambaldi/32/46108_2.png) [@MARCO\_RAMBALDI](https://discuss.elastic.co/u/MARCO_RAMBALDI)\
**Post date:** [June 14, 2019, 12:37pm UTC](https://discuss.elastic.co/t/insert-complex-nested-json-documents-from-postgres-to-elasticsearch-via-logstash/185813/3 "2019-06-14T12:37:12Z")

</div>

yes, it is! I followed this guide to get started:

> **[INSERT INTO LOGSTASH SELECT DATA FROM DATABASE](https://www.elastic.co/blog/logstash-jdbc-input-plugin)**
>
> Ever want to search your database entities from Elasticsearch? Introducing the JDBC input — import data from any database that supports the JDBC interface

  
where they put a document id so I also put it in my case.  
Perhaps it is not necessary, but this has not caused me problems for the moment.

---

<div class="post-metadata">

**Author:** ![Divit\_Sharma](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/divit_sharma/32/32348_2.png) [@Divit\_Sharma](https://discuss.elastic.co/u/Divit_Sharma)\
**Post date:** [June 14, 2019, 12:40pm UTC](https://discuss.elastic.co/t/insert-complex-nested-json-documents-from-postgres-to-elasticsearch-via-logstash/185813/4 "2019-06-14T12:40:23Z")

</div>

Remove the document\_id and re run it. It could be possible that uid is same hence it is being overwritten

---

<div class="post-metadata">

**Author:** ![MARCO\_RAMBALDI](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/marco_rambaldi/32/46108_2.png) [@MARCO\_RAMBALDI](https://discuss.elastic.co/u/MARCO_RAMBALDI)\
**Post date:** [June 14, 2019, 1:59pm UTC](https://discuss.elastic.co/t/insert-complex-nested-json-documents-from-postgres-to-elasticsearch-via-logstash/185813/5 "2019-06-14T13:59:07Z")

</div>

Thank you @Divit_Sharma!! That was the problem, the uid was about the tutorial data and not about my data.  
I just changed with my id data and now it works.  
Thanks,  
Marco

---

<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:** [July 12, 2019, 1:59pm UTC](https://discuss.elastic.co/t/insert-complex-nested-json-documents-from-postgres-to-elasticsearch-via-logstash/185813/6 "2019-07-12T13:59:16Z")

</div>

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