JSON fields imports from postgres to elasticsearch

Question

I'm trying to import some data with nested objects from postgres to elasticsearch using json_agg, but ES don't save data like nested objects, but save like this

"some_objects": {"type": "json", "value": "[{\"a\":\"1\"}]"}

instead:

"some_objects": [{"a":"1"}]

It happens every time, when I use json type in query (I'm importing data via "logstash"). If I need import joined object I use hstore and it's works, but I can't use hstore for multiple objects, and unfortunately I can't array instead json.

The SQL I'm using for import data looks like this

SELECT a.*,  
    (SELECT json_agg(hstore(so)) AS some_objects FROM
        (SELECT * FROM c WHERE a.id=c.a_id) AS so),
(SELECT hstore(ao) AS another_object FROM (SELECT b.*) AS ao),
FROM a_table AS a
LEFT OUTER JOIN b ON a.id=b.a_id

In a mapping data for elasticsearch I set:

...
"some_objects": {
    "type": "nested" 
},
"b": {
    "type": "object"
}

And it successfully works for "b", and doesn't work for "some_objects". Obviously it depend to json type in postgres. Any ideas how to I can solve it?

Great thanks.

It's looks like this http://stackoverflow.com/questions/21081196/jdbc-elasticsearch-and-postgresql-json-data-type-solved, but I does not use the river — Alexey, Jan 20 '16 at 04:52

score 2 · Accepted Answer · answered May 17 '16 at 12:05

It was solved by ruby filter.

filter {
    ruby {
        code => "
            require 'json'
            some_json_field_value = JSON.parse(event['some_json_field'].to_s)
            event['some_json_field'] = some_json_field_value
        "
    }
}

JSON fields imports from postgres to elasticsearch

1 Answers1