Is there any way I can query a text in rails with db postgres

Question

Here is the table structure

create_table :audits, :force => true do |t|
    t.column :audited_changes, :text
end

class Audit < ::ActiveRecord::Base
    serialize :audited_changes, Hash
end

This is bunch of code from gem Audited gem where I need to query text field audited_changes which is stored as serialized hash.

Found a reference where we can query for serialized array in text Searching serialized data, using active record, Do I have any option similar to that for querying rather that looping tons of records?

This is how I'm taking out the records I need from a audited_changes

fields = ['name', 'description']
audit_data = audit_data.select do |obj|
    obj.define_singleton_method(:audited_changes_filtered) do
        obj.audited_changes.select {|k,v| fields.map(&:to_s).include?(k) }
    end
    obj if fields_present?(obj, fields)
end

Do you have to use `serialize`? That's a horrible hack and parsing YAML inside the database is going to be a big mess. — mu is too short, Oct 25 '16 at 16:30
Ya @muistooshort Its a gem concept, I can't change now as the db already has more than 50k records — Madhusudhan, Oct 26 '16 at 12:37

score 0 · Answer 1 · answered Oct 25 '16 at 12:10

Query raw content of the serialized text column (may work only in some cases)

Assuming there are are two records:

Audit.create(audited_changes: {'key1' => 'value1', 'key2' => 'value2'})
Audit.create(audited_changes: {'key3' => 'value3', 'key4' => 'value4'})

Check how it looks in psql (how values are stored in PostgreSQL):

SELECT * FROM audits;
 id | audited_changes 
----+-----------------
  1 | ---            +
    | key1: value1   +
    | key2: value2   +
    | 
  2 | ---            +
    | key3: value3   +
    | key4: value4   +
    | 
(2 rows)

So values serialized in YAML

Query raw content of the text column to search record with key key3:

Audit.where("audited_changes LIKE '%\nkey3:%'")

This probably will work in your case.

Also you need an index for full-text search for fast querying.

tried this, didn't work. Do I have any other option other than looping the data? User can specify what field changes they need to see, according to that I need to restrict the data — Madhusudhan, Oct 26 '16 at 12:38

Is there any way I can query a text in rails with db postgres

1 Answers1

Query raw content of the serialized text column (may work only in some cases)