# Elasticsearch result: Array to CSV

**URL:** <https://discuss.elastic.co/t/elasticsearch-result-array-to-csv/224473>\
**Category:** Logstash\
**Created:** [March 20, 2020, 6:44pm UTC](https://discuss.elastic.co/t/elasticsearch-result-array-to-csv/224473 "2020-03-20T18:44:38Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Sineos](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sineos/32/64825_2.png) [@Sineos](https://discuss.elastic.co/u/Sineos)\
**Post date:** [March 20, 2020, 6:44pm UTC](https://discuss.elastic.co/t/elasticsearch-result-array-to-csv/224473/1 "2020-03-20T18:44:38Z")

</div>

I'm reading from a file and search the file's content in ES. The output of ES should go into a CSV.

**Conf file:**

```auto
    input {
      file {
        path => "d:/uae.txt"
        start_position => "beginning"
        sincedb_path => "NUL"
        mode => "tail"
      }
    }

    filter {
      elasticsearch {
        hosts => "http://localhost:9200"
        query_template => "c:/logstash/config/search.json"
        index => "address"
        fields => {
          "City" => "[City]"
          "State" => "[State]"
        }
      }
    }

    output { 
      csv {
        fields => ["City", "State"]
        path => "d:/result.txt"
      }
      stdout {codec => rubydebug}
    }

```

**Result stdout:**

```auto
    {
              "path" => "d:/uae.txt",
        "@timestamp" => 2020-03-20T18:28:25.396Z,
             "State" => [
            [0] "MO",
            [1] "MO"
        ],
           "message" => "home",
          "@version" => "1",
              "host" => "DESKTOP-H3VM3G3",
              "City" => [
            [0] "Branson",
            [1] "Hollister"
        ]
    }

```

**Result CSV:**

`"[""Branson"", ""Hollister""]","[""MO"", ""MO""]"`

**Expected result:**

```
"Branson", "MO"
"Hollister", "MO"

```

How can I have logstash transform the array result into a proper CSV?

---

<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:** [March 22, 2020, 5:20pm UTC](https://discuss.elastic.co/t/elasticsearch-result-array-to-csv/224473/2 "2020-03-22T17:20:15Z")

</div>

You could rearrange data using a ruby filter similar to [this](https://discuss.elastic.co/t/xml-filter-how-to-create-array-of-objects-using-xml-filter-xpath/144143/4). Then use a split filter.

---

<div class="post-metadata">

**Author:** ![Sineos](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sineos/32/64825_2.png) [@Sineos](https://discuss.elastic.co/u/Sineos)\
**Post date:** [March 22, 2020, 8:34pm UTC](https://discuss.elastic.co/t/elasticsearch-result-array-to-csv/224473/3 "2020-03-22T20:34:43Z")

</div>

Many thanks @Badger, it is working.

For reference, if someone should encounter the same:

```auto
input {
   file {
      path => "d:/uae.txt"
      start_position => "beginning"
      sincedb_path => "NUL"
      mode => "tail"
   }
}

filter {
   elasticsearch {
      hosts => "http://localhost:9200"
      query_template => "c:/logstash/config/search.json"
      index => "address"
      fields => {
         "City" => "City"
         "State" => "State"
      }
   }
}

filter {
   ruby { code => "
         c = event.get('City')
         s = event.get('State')
         a = []
         c.each_index { |k|
               h = { 'city' => c[k], 'state' => s[k] }
               a << h
         }
         event.set('arrayOfHashes', a)
   "
   }
}

filter {
   split {
      field => "arrayOfHashes"
   }
}

output { 
   csv {
      csv_options => {
         "col_sep" => ";" 
         "force_quotes" => true
      }
      fields => ["[arrayOfHashes][city]", "[arrayOfHashes][state]"]
      path => "d:/result.txt"
   }
   stdout {codec => rubydebug}
}

```

---

<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:** [April 19, 2020, 8:34pm UTC](https://discuss.elastic.co/t/elasticsearch-result-array-to-csv/224473/4 "2020-04-19T20:34:46Z")

</div>

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