# Postgres output from json type doesn't work, writes null values

**URL:** <https://discuss.elastic.co/t/postgres-output-from-json-type-doesnt-work-writes-null-values/218548>\
**Category:** Logstash\
**Created:** [February 10, 2020, 9:25am UTC](https://discuss.elastic.co/t/postgres-output-from-json-type-doesnt-work-writes-null-values/218548 "2020-02-10T09:25:34Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![ActionAdi83](https://avatars.discourse-cdn.com/v4/letter/a/8c91f0/32.png) [@ActionAdi83](https://discuss.elastic.co/u/ActionAdi83)\
**Post date:** [February 10, 2020, 9:25am UTC](https://discuss.elastic.co/t/postgres-output-from-json-type-doesnt-work-writes-null-values/218548/1 "2020-02-10T09:25:34Z")

</div>

Hello,

I need a little help, I'm trying to write from a kafka topic to a postgres table only specific fields and it doesn't work, it writes nulls. I haven't figured out how to post jsons to kafka topic so I'm getting them from a folder for now. I also tried formatting the json as single line since I was getting a separate message for each line in postgres. Also I think that kafka messages will be formatted as single line when I figure out how to post json messages, right? Anyways, here's my config:

input{  
file{  
path=\>"C:/Work/kafka-elasticsearch-connector/\*.json"  
start\_position=\>"beginning"  
}  
}  
output {  
jdbc {  
connection\_string =\> "jdbc:postgresql://localhost:5432/test?user=postgres&password=password"  
statement =\> ["INSERT INTO schema.logstashdata (topic, sentutc) VALUES(?, ?)", "topicName", "sentUtc"]  
}  
stdout {  
codec =\> rubydebug  
}  
}

Here's an example of json I am using as test:

{  
"header": {  
"topicName": "TOPIC1",  
"topicVer1": 1,  
"sentUtc": "2020-01-27T14:07:10Z",  
"status": "Test",  
"msgType": "Update",  
"code": ["status"]  
},  
"body": {  
"location": {  
"lat": 40.9999990,  
"lon": 29.9999997  
},  
"status": {  
"general": "OK",  
},  
"tasks": [{  
"id": "task-0001",  
"status": "Read"  
}]  
}  
}  
What am I doing wrong? Also I will need to add whole json as a field, I created a jsonb column in my table, how to do it?

Many thanks in advance,  
Adrian.

---

<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:** [February 10, 2020, 3:14pm UTC](https://discuss.elastic.co/t/postgres-output-from-json-type-doesnt-work-writes-null-values/218548/2 "2020-02-10T15:14:18Z")

</div>

If you have the entire JSON object as a single line in a file then the file filter will create a field called [message] that contains the entire JSON. You can use that to populate the jsonb column. You will need a json filter to parse the JSON. You will then be able to refer to [header][topicName] and [header][sentUtc].

---

<div class="post-metadata">

**Author:** ![ActionAdi83](https://avatars.discourse-cdn.com/v4/letter/a/8c91f0/32.png) [@ActionAdi83](https://discuss.elastic.co/u/ActionAdi83)\
**Post date:** [February 10, 2020, 4:10pm UTC](https://discuss.elastic.co/t/postgres-output-from-json-type-doesnt-work-writes-null-values/218548/3 "2020-02-10T16:10:24Z")

</div>

Thanks, I thought it would be an easier solution than that. Meanwhile I hardcoded jsons to kafka 🙂

I have this filter that worked for a write from kafka to elastic, will work from there:

filter {  
grok {  
match =\> { "message" =\> "%{GREEDYDATA:timestamp} %{LOGLEVEL:log-level} [%{DATA:link}] %{DATA:class} %{GREEDYDATA:message}" }  
}  
}

On a closer look I think I need the json filter, do you have an example please? for the [header][topicName] part, can't find any atm.

---

<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:** [February 10, 2020, 7:14pm UTC](https://discuss.elastic.co/t/postgres-output-from-json-type-doesnt-work-writes-null-values/218548/4 "2020-02-10T19:14:36Z")

</div>

> [@ActionAdi83](#):
>
> On a closer look I think I need the json filter, do you have an example please?

```
json { source = > "message" }

```

---

<div class="post-metadata">

**Author:** ![ActionAdi83](https://avatars.discourse-cdn.com/v4/letter/a/8c91f0/32.png) [@ActionAdi83](https://discuss.elastic.co/u/ActionAdi83)\
**Post date:** [February 11, 2020, 10:22am UTC](https://discuss.elastic.co/t/postgres-output-from-json-type-doesnt-work-writes-null-values/218548/5 "2020-02-11T10:22:25Z")

</div>

Thanks for the help badger, I finally got to this result:

left the filter as you said:  
filter{  
json{  
source =\> "message"  
}  
}

But figured out how to identify the data directly in output as below:

statement =\> ["INSERT INTO schema.top (topic, message) VALUES(?, ?)", "[header][topicName]", "message" ]

I am trying to send the whole kafka message as last column(message), I have it as character varying in postgres, what am I doing wrong? Is it not identified with "message"? I'm getting no error but the result in postgress is NULL.

Edit:  
Solved it, updated the filter to this:

filter{  
json{  
source =\> "message"  
}  
ruby { code =\> ' event.set("everything", event.to\_json) ' }  
}

and the output insert to this:

statement =\> ["INSERT INTO schema.topic (topic, message) VALUES(?, ?)", "[header][topicName]", "%{everything}" ]

---

<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:** [March 10, 2020, 10:22am UTC](https://discuss.elastic.co/t/postgres-output-from-json-type-doesnt-work-writes-null-values/218548/6 "2020-03-10T10:22:29Z")

</div>

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