Elasticsearch result: Array to CSV

[Sineos]

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:

    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:

    {
              "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?

[Badger]

You could rearrange data using a ruby filter similar to this. Then use a split filter.

[Sineos]

Many thanks @Badger, it is working.

For reference, if someone should encounter the same:

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}
}