Solved

Write to ElasticSearch JSON directly

  • 1 September 2017
  • 6 replies
  • 2 views

Badge +10

Hi,

I would like to write to elasticsearch, not from a table but from some JSON instead. I have prepared the JSON before hand because it has to be nested.

The output format is like so:

{    "mappings": {
      "storymap": {
        "properties": {
          "storyname": {
            "type": "text"
          },
          "storyid": {
            "type": "integer"
          },
          "storyjson": {
            "type": "text"
          },
          "categoryname": {
            "type": "text"
          },
          "subcategoryname": {
            "type": "text"
          },
         "regions": {
           "type": "nested",
           "properties": {
             "region_id": {
               "type": "integer"
             }
           }
         }
        }
      }
    }

I have managed to get this output format using the JSONTemplater - thanks to @petrahammoser 

your post was helpful.

 

0684Q00000ArCTGQA3.png

But now I want to pass the JSON to an index in elasticsearch which is already created.

I have tried but I am at a loss, the JSON doesn't seem to populate and I just get an empty index. Some example excel source files and workbench is attached.

If you can offer any help that would be fantastic.

Many Thanks,

Oliver

icon

Best answer by jakemolnar 1 September 2017, 18:57

View original

6 replies

Badge +10

checking with fiddler it is just parsing empty content

I set the config to JSON Attribute:

  • JSON Attribute: Documents will be created using JSON directly from an attribute, if possible.

>Note: The Elasticsearch writer will still create a Type Mapping based on the Feature Type User Attributes. If this is undesirable, then in the User Attributes tab, set Attribute Definition to Manual and remove all attributes from the feature type.

Set to Manual, removed the surplus fields.

xlsxr-xlsxr2elasticsearch-elasticsearch-copy.fmw

{"index" :{"_index":"storymaptest", "_type":"test"}}{ }{"index" :{"_index":"storymaptest", "_type":"test"}}{ }{"index" :{"_index":"storymaptest", "_type":"test"}}{ }{"index" :{"_index":"storymaptest", "_type":"test"}}{ }
Badge

Have you tried renaming the `_json` attribute? (`_json` is a reserved attribute name in the driver used by the Elasticsearch Writer)

I think you should be alright if you:

  1. Change the result attribute name on the JSONTemplater to `_json_result`
  2. Change the JSON Attribute name in the Elasticsearch Writer Parameters to match
Badge +11

Try lowercase attribute names on the writer. I'm testing that and seems to help.

Badge +11

Hi @olivermorris

With the help of Developer Jake, we have come up with this workspace.

 

xlsxr-xlsxr2elasticsearch-safe.fmw

See if that helps. I've embedded a few comments of the changes made.

Steve.

Badge +10

Have you tried renaming the `_json` attribute? (`_json` is a reserved attribute name in the driver used by the Elasticsearch Writer)

I think you should be alright if you:

  1. Change the result attribute name on the JSONTemplater to `_json_result`
  2. Change the JSON Attribute name in the Elasticsearch Writer Parameters to match

 

Thank you very much this did the trick worked immediately afterwards.
Badge +10

Hi @olivermorris

With the help of Developer Jake, we have come up with this workspace.

 

xlsxr-xlsxr2elasticsearch-safe.fmw

See if that helps. I've embedded a few comments of the changes made.

Steve.

 

Thank you for the worksheet, I looked through this and I understand your commends regarding grouping, I will include these going forward to avoid duplicates as well.

 

 

Thanks again

Reply