# Inserting a Complex Nested Json from Postgres to Elasticsearch via Logstash

**URL:** <https://discuss.elastic.co/t/inserting-a-complex-nested-json-from-postgres-to-elasticsearch-via-logstash/58405>\
**Category:** Logstash\
**Created:** [August 18, 2016, 10:25pm UTC](https://discuss.elastic.co/t/inserting-a-complex-nested-json-from-postgres-to-elasticsearch-via-logstash/58405 "2016-08-18T22:25:43Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![anushav85](https://avatars.discourse-cdn.com/v4/letter/a/e68b1a/32.png) [@anushav85](https://discuss.elastic.co/u/anushav85)\
**Post date:** [August 18, 2016, 10:25pm UTC](https://discuss.elastic.co/t/inserting-a-complex-nested-json-from-postgres-to-elasticsearch-via-logstash/58405/1 "2016-08-18T22:25:43Z")

</div>

Hi All,

I am inserting a table into ES which contains a column that is of type Nested Jsonb (I am converting it to Json while extracting the data) in Postgres.  
This is my logstash conf file:

input {  
jdbc {  
jdbc\_connection\_string =\> "jdbc:postgresql://myhostname:5432/mydb"  
jdbc\_user =\> "myusr"  
jdbc\_password =\> "mypwd"  
jdbc\_validate\_connection =\> true  
jdbc\_driver\_library =\> "/ELK/postgres/postgresql-9.4.1208.jar"  
jdbc\_driver\_class =\> "org.postgresql.Driver"  
statement =\> "SELECT to\_json(business\_det) from business limit 1"  
}  
}

filter{  
json{  
source =\> "%{[to\_json][value]}"  
}  
}

output {  
elasticsearch {  
index =\> "abc"  
document\_type =\> "details"  
# document\_id =\> "%{business\_id}"  
# hosts =\> "localhost"  
}  
stdout{}  
}

Ideally, I would like the Nested Json object (business\_det) to be stored in the same nested json format in root document in ES.  
But it's being stored in the field "value" within "to\_json" and I am unable to do the above.  
Here's how it looks:

{  
"took" : 4,  
"\_shards" : {  
"failed" : 0,  
"successful" : 5,  
"total" : 5  
},  
"timed\_out" : false,  
"hits" : {  
"hits" : [  
{  
"\_score" : 1,  
"\_index" : "abc",  
"\_source" : {  
"@version" : "1",  
"@timestamp" : "2016-08-18T21:45:29.222Z",  
"to\_json" : {  
"value" : "{"gps": {"latitude": "41.15432", "longitude": "-74.35413"}, "name": "US Post Office", "aboutMe": "", "address": {"city": "Hewitt", "line1": "1926 Union Valley Rd Ste 3", "state": "NJ", "country": "USA", "zipCode": "07421"}, "category": "Post Offices", "phoneNum": [{"work": "(800) 275-8777"}], "openHours": ["Mon - Fri 8:30 am - 5:00 pm", " Sat 8:30 am - 12:30 pm", " Sun Closed"], "searchTags": [" Post Offices", "Mail & Shipping Services"]}",  
"type" : "json"  
}  
},  
"\_type" : "details",  
"\_id" : "AVafnb8ftbvkzTn2myuI"  
}

Here's my mapping:

curl -XPUT "[http://localhost:9200/abc/\_mapping/details](http://localhost:9200/abc/_mapping/details)" -d'  
{  
"details": {  
"properties": {  
"to\_json": {  
"type": "nested",  
"properties": {  
"name": {  
"type" : "string",  
"analyzer": "autocomplete"  
},  
"category": {  
"type" : "string",  
"analyzer": "autocomplete"  
},  
"aboutMe": {  
"type" : "string",  
"analyzer": "autocomplete"  
},  
"searchTags": {  
"type" : "string",  
"analyzer": "autocomplete"  
}  
}  
}  
}  
}  
}'

What am I doing wrong here?  
Please let me know.

Thanks.

---

<div class="post-metadata">

**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\
**Post date:** [August 19, 2016, 7:30am UTC](https://discuss.elastic.co/t/inserting-a-complex-nested-json-from-postgres-to-elasticsearch-via-logstash/58405/2 "2016-08-19T07:30:08Z")

</div>

Are you sure the result above came from when you had the json filter in place? Because this looks totally fine and should work. Is there anything in the logs? In the unlikely event of a screwed-up JSON string Logstash will log a message about it.

---

<div class="post-metadata">

**Author:** ![anushav85](https://avatars.discourse-cdn.com/v4/letter/a/e68b1a/32.png) [@anushav85](https://discuss.elastic.co/u/anushav85)\
**Post date:** [August 19, 2016, 3:17pm UTC](https://discuss.elastic.co/t/inserting-a-complex-nested-json-from-postgres-to-elasticsearch-via-logstash/58405/3 "2016-08-19T15:17:41Z")

</div>

Yes, I am sure I used the same logstash conf file for the result above.  
I checked the logs, there was no error or exception. Just to be sure, I did it all again and checked it. Cannot put my finger on it.  
By the way, I tried loading the data as a JSON file. It worked perfectly fine!  
Just when I import it from Postgres, I am running into this trouble.

Please advise.  
Thanks again.

---

<div class="post-metadata">

**Author:** ![anushav85](https://avatars.discourse-cdn.com/v4/letter/a/e68b1a/32.png) [@anushav85](https://discuss.elastic.co/u/anushav85)\
**Post date:** [August 25, 2016, 9:28pm UTC](https://discuss.elastic.co/t/inserting-a-complex-nested-json-from-postgres-to-elasticsearch-via-logstash/58405/4 "2016-08-25T21:28:17Z")

</div>

Ok, here goes! I was able to resolve this issue.  
Had to import the data from Postgres in text format and apply json filter on the respective fields.

SELECT postgres\_parent\_json\_object-\>\>'postgres\_child\_json\_tag' FROM postgres\_table

It worked just fine! 🙂

Oh yeah, when dealing with arrays, logstash reads the field names in lowercase format (camelcasing does not work, so make sure the source field name is in lowercase).  
Hope this helps people who are stuck with reading nested json from Postgres.

---

<div class="post-metadata">

**Author:** ![sumankar123](https://avatars.discourse-cdn.com/v4/letter/s/aca169/32.png) [@sumankar123](https://discuss.elastic.co/u/sumankar123)\
**Post date:** [November 2, 2016, 9:59pm UTC](https://discuss.elastic.co/t/inserting-a-complex-nested-json-from-postgres-to-elasticsearch-via-logstash/58405/5 "2016-11-02T21:59:19Z")

</div>

Hi @anushav85,

Could you kindly share the working version of the input and filter sections of your logstash configuration file? I am specifically trying to understand your 'import the data from Postgres in text format' comment.

I have a similar situation where I'm using a SQL file as my input source, and the records have a few fields that are of type JSON, that are not being interpretted by Elasticsearch properly.

Here is a simple document. Checkout the fields, instances and customfields fields. These are JSON fields in the database.

{  
"took" : 7,  
"timed\_out" : false,  
"\_shards" : {  
"total" : 5,  
"successful" : 5,  
"failed" : 0  
},  
"hits" : {  
"total" : 1,  
"max\_score" : 4.663562,  
"hits" : [ {  
"\_index" : "metadata",  
"\_type" : "dataset",  
"\_id" : "1470",  
"\_score" : 4.663562,  
"\_source" : {  
"id" : 1470,  
"name" : "JK Dataset 5000",  
"team" : "The A-Team",  
"owner\_name" : "Seat-Pacifico@here.com",  
"steward\_name" : "jonathan.kurz@here.com",  
"contact\_email" : "jonathan.kurz@here.com",  
"description" : "The first dataset I've added since Amplitude logging was implemented.",  
"pii" : "no",  
"quality" : null,  
"frequency" : null,  
"environment" : null,  
"external\_doc\_url" : null,  
"sample\_data\_location" : null,  
"current\_version" : null,  
"create\_date" : "2016-11-02T17:24:35.590Z",  
"last\_change\_date" : "2016-11-02T18:36:34.214Z",  
"active" : true,  
"hidden" : false,  
"classification" : "HERE Internal Use Only",  
"licenses" : null,  
"schema\_uri" : null,  
"hidden\_reason" : null,  
"created\_at" : "2016-11-02T17:24:35.591Z",  
"updated\_at" : "2016-11-02T18:36:34.216Z",  
"fields" : {  
"type" : "json",  
"value" : "[{"dataset\_id":1470,"id":627,"name":"field12`","description":"adfs","common\_field":null,"pii":"no","created\_at":"2016-11-02T17:25:30.551341+00:00","updated\_at":"2016-11-02T17:25:30.551341+00:00"}]"  
},  
"customfields" : {  
"type" : "json",  
"value" : "[{"id":52,"dataset\_id":1470,"namespace":"default","key":"new attribute with brackets []","value":"[asdfasdf,3,4,5]","created\_at":"2016-11-02T17:27:35.413789+00:00","updated\_at":"2016-11-02T17:27:35.413789+00:00"}]"  
},  
"tags" : null,  
"instances" : {  
"type" : "json",  
"value" : "[{"instance\_id":1559,"instance\_name":null,"instance\_description":"no description","source\_uri":"testURI123","source\_name":"JK Amplitude source","source\_uri\_hint":"no [hint","source\_owner":"jonathan.kurz@here.com](mailto:hint%22,%22source_owner%22:%22jonathan.kurz@here.com)"}]"  
},  
"@version" : "1",  
"@timestamp" : "2016-11-02T21:31:01.408Z"  
}  
} ]  
}  
}

Here is my logstash config file,

input {  
jdbc {  
# Postgres jdbc connection string to our database, mydb  
jdbc\_connection\_string =\> "Replace with db name"  
# The user we wish to execute our statement as  
jdbc\_user =\> "Replace with user"  
jdbc\_password =\> "Replace with pwd"  
# The path to our downloaded jdbc driver  
jdbc\_driver\_library =\> "/home/metadata/postgres/postgresql-9.4-1201-jdbc42-20150827.124843-3.jar"  
# The name of the driver class for Postgresql  
jdbc\_driver\_class =\> "org.postgresql.Driver"  
# our query  
statement\_filepath =\> "/home/metadata/logstash/query.sql"  
# schedule  
schedule =\> "\* \* \* \* \*"  
}  
}  
filter {  
json {  
source =\> "[instances][value]"  
}  
}

output {  
elasticsearch {  
index =\> "metadata"  
document\_type =\> "dataset"  
document\_id =\> "%{id}"  
hosts =\> ["localhost"]  
}  
}

Regards,  
Suman

---

<div class="post-metadata">

**Author:** ![anushav85](https://avatars.discourse-cdn.com/v4/letter/a/e68b1a/32.png) [@anushav85](https://discuss.elastic.co/u/anushav85)\
**Post date:** [November 2, 2016, 10:32pm UTC](https://discuss.elastic.co/t/inserting-a-complex-nested-json-from-postgres-to-elasticsearch-via-logstash/58405/6 "2016-11-02T22:32:50Z")

</div>

Could you share the SQL statement in your "query.sql"?  
By 'importing the data in text format' I meant logstash didn't work with the nested JSON object stored in our Postgres table for me, so I had to convert it to text by using the statement I had mentioned in my earlier post.

---

<div class="post-metadata">

**Author:** ![sumankar123](https://avatars.discourse-cdn.com/v4/letter/s/aca169/32.png) [@sumankar123](https://discuss.elastic.co/u/sumankar123)\
**Post date:** [November 2, 2016, 11:05pm UTC](https://discuss.elastic.co/t/inserting-a-complex-nested-json-from-postgres-to-elasticsearch-via-logstash/58405/7 "2016-11-02T23:05:06Z")

</div>

My SQL is somewhat long but here you go..

SELECT  
dm.\*,  
(  
SELECT array\_to\_json(array\_agg(row\_to\_json(f)))  
FROM (  
SELECT \*  
FROM application.fields\_master fm  
WHERE fm.dataset\_id = [dm.id](http://dm.id)  
) f  
) AS fields,  
(  
SELECT array\_to\_json(array\_agg(row\_to\_json(c)))  
FROM (  
SELECT \*  
FROM application.custom\_fields\_master cfm  
WHERE cfm.dataset\_id = [dm.id](http://dm.id)  
) c  
) AS customfields,  
(  
SELECT array\_to\_json(array\_agg(row\_to\_json(t)))  
FROM (  
SELECT tm.text  
FROM application.tags\_dataset\_mapping tdm  
INNER JOIN application.tags\_master tm  
ON tdm.dataset\_id = [dm.id](http://dm.id)  
AND tdm.tag\_id = [tm.id](http://tm.id)  
) t  
) AS tags,  
(  
SELECT array\_to\_json(array\_agg(row\_to\_json(i)))  
FROM (  
SELECT  
[di.id](http://di.id) AS instance\_id,  
[di.name](http://di.name) AS instance\_name,  
di.description AS instance\_description,  
di.source\_uri,  
s.source\_name,  
s.source\_uri\_hint,  
s.owned\_by AS source\_owner  
FROM application.dataset\_instance di  
INNER JOIN application.sources s  
ON di.dataset\_id = [dm.id](http://dm.id)  
AND di.source\_id = [s.id](http://s.id)  
) i  
) AS instances  
FROM application.dataset\_master dm

---

<div class="post-metadata">

**Author:** ![sumankar123](https://avatars.discourse-cdn.com/v4/letter/s/aca169/32.png) [@sumankar123](https://discuss.elastic.co/u/sumankar123)\
**Post date:** [November 2, 2016, 11:09pm UTC](https://discuss.elastic.co/t/inserting-a-complex-nested-json-from-postgres-to-elasticsearch-via-logstash/58405/8 "2016-11-02T23:09:46Z")

</div>

Here is a sample output record for the SQL query in my previous post. The last 4 fields are JSON type.

CREATE TABLE "MY\_TABLE" (  
id bigint,  
name varchar,  
team varchar,  
owner\_name varchar,  
steward\_name varchar,  
contact\_email varchar,  
description varchar,  
pii varchar,  
quality varchar,  
frequency varchar,  
environment varchar,  
external\_doc\_url varchar,  
sample\_data\_location varchar,  
current\_version varchar,  
create\_date timestamp,  
last\_change\_date timestamp,  
active bool,  
hidden bool,  
classification varchar,  
licenses varchar,  
schema\_uri varchar,  
hidden\_reason varchar,  
created\_at timestamptz,  
updated\_at timestamptz,  
fields json,  
customfields json,  
tags json,  
instances json  
);

INSERT INTO "MY\_TABLE"(id, name, team, owner\_name, steward\_name, contact\_email, description, pii, quality, frequency, environment, external\_doc\_url, sample\_data\_location, current\_version, create\_date, last\_change\_date, active, hidden, classification, licenses, schema\_uri, hidden\_reason, created\_at, updated\_at, fields, customfields, tags, instances) VALUES (503, 'SK\_Dataset\_100', 'Insights', 'suman.kar@here.com', 'suman.kar@here.com', 'suman.kar@here.com', 'blah', 'yes', null, null, null, null, null, null, '2016-10-11 04:25:43.468000', '2016-10-14 00:16:24.735000', true, false, 'Confidential', null, null, null, '2016-10-11 04:25:43.580424', '2016-10-14 00:16:24.845744', [{"dataset\_id":503,"id":50,"name":"eee","description":"eee","common\_field":null,"pii":"yes","created\_at":"2016-10-13T13:15:26.327249-07:00","updated\_at":"2016-10-13T13:15:52.759431-07:00"}], null, null, null);

---

<div class="post-metadata">

**Author:** ![Bikram\_KC](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/bikram_kc/32/13313_2.png) [@Bikram\_KC](https://discuss.elastic.co/u/Bikram_KC)\
**Post date:** [November 21, 2016, 9:00am UTC](https://discuss.elastic.co/t/inserting-a-complex-nested-json-from-postgres-to-elasticsearch-via-logstash/58405/10 "2016-11-21T09:00:04Z")

</div>

I had exact same problem. Got solution using filter as follows:

filter {  
ruby {  
code =\> "  
require 'json'  
some\_json\_field\_value = JSON.parse(event.get('some\_json\_field').to\_s)  
event.set('some\_json\_field',some\_json\_field\_value)  
"  
}  
}

---

<div class="post-metadata">

**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\
**Post date:** [November 21, 2016, 12:17pm UTC](https://discuss.elastic.co/t/inserting-a-complex-nested-json-from-postgres-to-elasticsearch-via-logstash/58405/11 "2016-11-21T12:17:27Z")

</div>

> I had exact same problem. Got solution using filter as follows:

What's the benefit of this over a json filter?

---

<div class="post-metadata">

**Author:** ![sumankar123](https://avatars.discourse-cdn.com/v4/letter/s/aca169/32.png) [@sumankar123](https://discuss.elastic.co/u/sumankar123)\
**Post date:** [November 21, 2016, 6:41pm UTC](https://discuss.elastic.co/t/inserting-a-complex-nested-json-from-postgres-to-elasticsearch-via-logstash/58405/12 "2016-11-21T18:41:36Z")

</div>

I actually figured that. Thanks for confirming though.

---

<div class="post-metadata">

**Author:** ![sumankar123](https://avatars.discourse-cdn.com/v4/letter/s/aca169/32.png) [@sumankar123](https://discuss.elastic.co/u/sumankar123)\
**Post date:** [November 21, 2016, 6:42pm UTC](https://discuss.elastic.co/t/inserting-a-complex-nested-json-from-postgres-to-elasticsearch-via-logstash/58405/13 "2016-11-21T18:42:19Z")

</div>

Not sure what the benefit is but I was just not able to get the json filter to work.

---

<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 6, 2017, 4:30am UTC](https://discuss.elastic.co/t/inserting-a-complex-nested-json-from-postgres-to-elasticsearch-via-logstash/58405/14 "2017-07-06T04:30:19Z")

</div>