# Postgres Logstash JSON parsing with JSON and Ruby

**URL:** <https://discuss.elastic.co/t/postgres-logstash-json-parsing-with-json-and-ruby/194146>\
**Category:** Logstash\
**Created:** [August 7, 2019, 5:44am UTC](https://discuss.elastic.co/t/postgres-logstash-json-parsing-with-json-and-ruby/194146 "2019-08-07T05:44:03Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![milanbgd011](https://avatars.discourse-cdn.com/v4/letter/m/b5a626/32.png) [@milanbgd011](https://discuss.elastic.co/u/milanbgd011)\
**Post date:** [August 7, 2019, 5:44am UTC](https://discuss.elastic.co/t/postgres-logstash-json-parsing-with-json-and-ruby/194146/1 "2019-08-07T05:44:03Z")

</div>

Spent a lot of time trying to debug this simple scenario, so here it is for others to use. I did try to find an answer here but everything was partial or not working, besides a ruby plugin implementation which is overkill.

This is set to import and keep all up to date based on "ru" field which stands for "record updated" and holds precise time of update.

Gotchas: in json field you CAN'T have \_id field, it is reserved!

FULLY WORKING CONFIG

```auto
input {
 jdbc {
  jdbc_driver_library => "/postgresql-42.2.6.jar"
  jdbc_driver_class => "org.postgresql.Driver"
  jdbc_connection_string => "jdbc:postgresql://localhost:32776/customdbs"
  jdbc_user => "postgres"
  jdbc_password => "root"
  schedule => "* * * * *"	
  statement => "SELECT _id as id, ru, rsearch, rjson::text as rjsontext FROM producers WHERE _id = '5b8ab4e0c3088f8de7a3634d' AND ru > :sql_last_value;"
  use_column_value => true
  tracking_column => "ru"
  tracking_column_type => "timestamp"
  record_last_run => true
  last_run_metadata_path => "record.last"
 }
}

# WORKING CODE BUT OVERKILL
# filter {
# ruby {
# code => "
# require 'json'
# rjson = JSON.parse(event.get('rjsontext').to_s)
# event.set('rjson',rjson)
# "
# }
# }

# NATIVE IMPLEMENTATION WORKING
filter{
 json{
  source => "rjsontext"
  target => "rjson"
  remove_field => ["rjsontext"]
 }
}

output {
 elasticsearch {
  hosts => "0.0.0.0:50100"
  index => "index1"
  document_id => "%{id}"
  action => "update"
  doc_as_upsert => true
 }
}

```

Cheers!

---

<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:** [September 4, 2019, 5:44am UTC](https://discuss.elastic.co/t/postgres-logstash-json-parsing-with-json-and-ruby/194146/2 "2019-09-04T05:44:24Z")

</div>

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