Rails select_all deserialize Postgresql array

Question

I make a custom query that returns rows with array value term_names

Product.connection.select_all("
  SELECT ARRAY_AGG(terms.name), vocabularies.name vocabulary_name
  FROM terms
  INNER JOIN vocabularies ON vocabularies.id = terms.vocabulary_id
  GROUP BY vocabulary_name")

| term_names                                                     | vocabulary_name |
|----------------------------------------------------------------|-----------------|
| {{76,Yellow},{77,Green},{79,Blue}.                             | Color           |

But the problem is that Rails does not want to convert {...} into ruby array and returns it as string.

[{"array_agg"=>"{Yellow,Green,Blue}", "vocabulary_name"=>"Color"}]

How can I make Rails parse result and return nested array instead?

You're pretty much using ruby wrongly ... But in any case you're asking your database to return a string. If you want it to be an array, you'll need to use split, and/or set up a model to talk to the "terms" table, then use ruby to do the grouping — Jad
– Jad, Commented Jun 26, 2021 at 16:16

Molfar · Accepted Answer · 2021-06-26 16:42:06Z

4

You (me) should call cast_values method

Product.connection.select_all("
  SELECT ARRAY_AGG(terms.name), vocabularies.name vocabulary_name
  FROM terms
  INNER JOIN vocabularies ON vocabularies.id = terms.vocabulary_id
  GROUP BY vocabulary_name").cast_values

answered Jun 26, 2021 at 16:42

Molfar

1,5013 gold badges19 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Yarin · Accepted Answer · 2023-08-17 16:32:43Z

0

I ran into this problem as well so I extended ActiveRecord::Result with a to_cast_hash method:

class ActiveRecord::Result

  # NOTE: calling .to_hash turns all values, including arrays, into strings, 
  # while calling .cast_values returns an array without the column keys.
  # This method returns a hash of the results with properly cast values:
  def to_cast_hash
    cast_rows = self.cast_values
    cols = self.columns
    cast_hashes = []
    cast_rows.each do |row|
      h = {}
      cols.each_with_index do |col,index|
        h[col] = row[index]
      end
      cast_hashes.push(h)
    end
    return cast_hashes
  end
  
end

answered Aug 17, 2023 at 16:32

Yarin

186k156 gold badges414 silver badges527 bronze badges

Collectives™ on Stack Overflow

Rails select_all deserialize Postgresql array

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related