Elasticsearch result to table

Question

I am using the gem elasticsearch-rails to retrieve data from elasticsearch in a dynamic way, meaning that the result can have none or multiple aggregations depending on users choices.

Imagine a response like this:

(...)
"aggregations"=>
{"agg_insignia_id"=>
  {"buckets"=>
    [{"key"=>1,
      "key_as_string"=>"1",
      "doc_count"=>32156,
      "agg_chain_id"=>
       {"buckets"=>
         [{"key"=>9,
           "key_as_string"=>"9",
           "doc_count"=>23079,
           "agg_store_id"=>
            {"buckets"=>
              [{"key"=>450,
                "key_as_string"=>"450",
                "doc_count"=>145,
                "agg_value"=>{"value"=>1785.13}},

               {"key"=>349,
                "key_as_string"=>"349",
                "doc_count"=>143,
                "agg_value"=>{"value"=>1690.37}},

How can I transform that data in a tabular data? like

|  insignia_id  |  chain_id  |  store_id  |  value   |
|  1            |  9         |  450       |  1785.13 |
|  1            |  9         |  349       |  1690.37 |
(...)

EDIT :: Being clear on the response I am looking for, two choices here: Array (simple) or Array of hashes.

Array style: [[insignia_id, chain_id, store_id, value], [1,9,450,1785.13], [1,9,349,1690.37],...]

Array of Hashes style: [{insignia_id => 1, chain_id => 9, store_id => 450, value => 1785.13}, {insignia_id => 1, chain_id => 9, store_id => 450, value => 1690.37 }]

The later is more like an activerecord style...

Yes, I am. The search query is something like this: Sale.search query: { filtered: { filter: { bool: { must: _conditions } } } }, aggs: _aggregations[:aggs], size: 0 — tostasqb, Aug 07 '14 at 12:03
Do you want to be able to export this table or is this going to be displayed in a view as a table? — settheline, Aug 07 '14 at 16:15
I want to be able to convert this in a array or hash...like a normal Activerecord response — tostasqb, Aug 08 '14 at 14:05
Hmmm I'm unclear about what exactly you want to do, since your response already looks like a hash. — settheline, Aug 08 '14 at 15:27

score 2 · Accepted Answer · answered Aug 11 '14 at 15:11

ok, so I came up with a solution for an array response.

Firstly added a helper for what comes ahead...

class Hash
  def deep_find(key, object=self, found=nil)
    if object.respond_to?(:key?) && object.key?(key)
      return object[key]
    elsif object.is_a? Enumerable
      object.find { |*a| found = deep_find(key, a.last) }
      return found
    end
  end
end

now for the array algorithm (added in a concern):

def self.to_table_array(data, aggs, final_table = nil, row = [])
    final_table = [aggs.keys] if final_table.nil?
    hash_tree = data.deep_find(aggs.keys.first)

    if aggs.values.uniq.length == 1 && aggs.values.uniq == [:data]
        aggs.keys.each do |agg|
            row << data[agg]["value"]
        end
        final_table << row
    else
        hash_tree["buckets"].each_with_index do |h, index|
            row.pop if index > 0
            aggs.shift if index == 0

            row << h["key_as_string"]
            final_table = to_table_array(h, aggs.clone, final_table, row.clone)
        end
    end

    final_table
end

The call for this method could be made like this:

#_fields = { "insignia_id" => :row, "chain_id" => :row, "store_id"=> :row, "value" => : data }
#res.response => Elasticsearch response
result = to_table_array(res.response, _fields)

There are some things quite specific to this case like you can see on this _fields variable. Also I'm assuming each aggregation has the name of the term itself. The rest is quite the same for every possible case.

A result of an array of hashes is pretty simple from here just by replacing few lines.

I put a lot of efford in this. Hope this helps someone else other than me.

Elasticsearch result to table

1 Answers1

Linked