Rails 6: strongly typing raw SQL Query returning array type

Question

For reasons that are not really relevant to the question, I've had to construct a fairly complex query manually rather than using ActiveRecord. This query currently returns a PostgreSQL array data type and I'm trying to see if I can map it to more useful Ruby types than what Rails is providing by default.

Pseudo-code below:

SELECT
  ARRAY_AGG(u.id) AS awesome_data
FROM u
GROUP BY condition

records = []
ActiveRecord::Base.connection.select_all(
      ActiveRecord::Base.send(:sanitize_sql_array, [
                                query,
        # parameters
                              ])
      ).each do |record|
        records << record
      end

This query works fine and returns what I would expect, but the type of the awesome_data column comes back as string and contains values like {1,2,3,4}.

Couple of questions:

• Is there any way to make Rails understand that the column is an array type and to parse it as an array?
• Is there a utility method that will parse this to an array of integers for me?
• Do I have to parse this value myself?

Restrictions:

We have to assume that its necessary to query the database in this fashion and not go through ActiveRecord.

Env: Rails 6.1, Ruby 3.2 and PostgreSQL 12

Answer 1

ActiveRecord::Result has cast_values method (it uses deserialize under the hood)

query = <~SQL
  SELECT ARRAY_AGG(u.id) AS awesome_data
  FROM u
  GROUP BY condition
SQL

ActiveRecord::Base.connection.exec_query(query).cast_values.flatten
# => [1, 2, 3, 4] # Ruby array of integers

If you want sanitize SQL, don't use send — call sanitize method directly

These methods are not private but public

ActiveRecord::Base.sanitize_sql([query])

You can also use Arel to generate sanitized SQL

u = Arel::Table.new('u')

query =
  u.project(Arel::Nodes::NamedFunction.new('ARRAY_AGG', [u[:id]]).as('awesome_data'))
   .group('condition')
   .to_sql