how to set a GSI for condition ?

[pribadi1st]

Hi, i want to ask a simple question how to set a GSI for a condition such as contains

i have a table called messages.rb

but when i do a query

Message.where('text_html.contains': "some text")

the console give me a warning

Queries without an index are forced to use scan and are generally much slower than indexed queries!
You can index this query by adding index declaration to messages.rb:
* global_secondary_index hash_key: 'some-name', range_key: 'some-another-name'
* local_secondary_index range_key: 'some-name'
Not indexed attributes: :text_html.contains

this is my messages.rb and how i define the attribute

class Message
  include Dynamoid::Document

  table name: :messages, key: :id, capacity_mode: :on_demand

  field :message, :string
  # global_secondary_index hash_key: 'some-name'
  field :creator_id, :string
  field :deleted_at, :datetime

  global_secondary_index name: 'message_index', hash_key: :message, projected_attributes: :all, range_key: :created_at,
                         capacity_mode: :on_demand
end

[andrykonchin]

Index may be used automatically but it's also possible to force it by calling with_index method (documentation)

For instance in your case it may look like:

Message.where('text_html.contains': "some text").with_index(:message_index)

But in this particular case using the index will not help as far as there is no any equality condition for the partition key attribute in the query. So such code will lead to a full table scan. It's how DynamoDB works.

You can find more details about the Query operation here.

[namdv-1375]

@andrykonchin If I want to search case-insensitively, how should I do it? Pls help me, thanks

[andrykonchin]

AFAIK DynamoDB doesn't provide means for case-insensitive String comparison. There are workarounds e.g. to store Strings in a separate attributes upper/lower-cased.