Search code examples
rubysequel

Using Sequel to convert the result of array_agg into hash or array

I have a complicated SQL which I run with DB[sql]. One of the columns is the result of array_agg applied to row (like this: array_agg(row(description, amount)::my_type)) and what I get from DB is a string as follows:

"{\"(aaaa,11111111.0000000000)\",\"(bbbbb,22222222.0000000000)\",\"(vvvvvv,33333333.0000000000)\"}"

I have Sequel on my project and I am aware of the pg_array and pg_row extensions but I haven't found a way of converting this string to a hash or array without using something dirty such as:

raw = "{\"(aaaa,11111111.0000000000)\",\"(bbbbb,22222222.0000000000)\",\"(vvvvvv,33333333.0000000000)\"}"
Hash[raw.delete('"(){}').split(',').each_slice(2).to_a].each do ... end

Is it possible to convert this string to hash or array using Sequel?

Solution

  • You can use the pg_row and pg_array extensions and have the result be returned as an array of hashes (or custom objects). Check the documentation for those extensions:

    http://sequel.jeremyevans.net/rdoc-plugins/files/lib/sequel/extensions/pg_row_rb.html http://sequel.jeremyevans.net/rdoc-plugins/files/lib/sequel/extensions/pg_array_rb.html

    This may be as simple as:

    DB.extension :pg_array, :pg_row
DB.register_row_type(:my_type)