Filter by map attribute

[ckhsponge]

I just came across a need to filter a query by a map attribute value. From what I can tell there's currently no way to do this with Dynamoid.

Here's an example:

class History
  include Dynamoid::Document
  field :details, :map
end

History.create!( details: {status: 'good', color: 'red'} )
History.where( 'details.status': 'good' ) # nope
History.where( 'details.status.eq': 'good' ) # still nope

Workaround:

History.all.to_a.filter{|h| h.details&[:status] == 'good'}

Here's what it could look like with the AWS SDK:

Dynamoid.adapter.client.query(
  table_name: History.table_name,
  index_name: 'some_index',
  key_condition_expression: "#_a1 = :_a1 AND range_column > :_a2",
  expression_attribute_values: {
    ":_a1" => "SomeHashValue" ,
    ":_a2" => some_range_value,
    ":_a3" => "good"
  },
  expression_attribute_names: {
    "#_a1" => "hash_column",
    "#_a3" => "details",
    "#_a4" => "status",
  },
  filter_expression: "#_a3.#_a4 = :_a3",
)

Does anyone have any other thoughts or ideas on this?

[andrykonchin]

As an option I would consider PartiQL (documentation). There is a limited support in Dynamoid so you can use it this way:

Dynamoid.adapter.execute('SELECT * FROM users WHERE id = ?', ['1'])

The only issue with PartiQL I see now is that Dynamoid does't support pagination of ExecuteStatement's result.

There is a plan to accept a keys/filter expression as a #where's argument directly (e.g. User.where("details.status = 'bar'")).

It isn't easy to simply allow referencing list elements and map attributes in #where (I mean something like this User.where("settings.foo" => "bar")) because to allow special symbols in attribute names they are sent in a request in expression_attribute_names and replaced with placeholders. But referencing a map attribute would require 2 placeholders at least - for a map and for an attribute name. And this . can be both - a part of an attribute name and a delimiter between a map and its attribute name.

As a solution I am considering allowing a user to specify placeholders on his own, e.g. this way - User.where("#a.#b" => "bar").placeholders("#a" => "details", "#b" => "status").

What do you think about it?

There is a similar issue for referencing list elements (#684).

[ckhsponge]

Thanks for suggesting PartiQL. It works great for what I need to do.

To continue my example, with PartiQL it would be:

Dynamoid.adapter.execute("SELECT * FROM #{History.table_name} WHERE hash_column = ? AND range_column > ? AND details.status = ?", ["HashValue", range_value, "Good"])

I think allowing a filter_expression to be added directly would be good. You could allow expression_attribute_values and expression_attribute_names to be added directly as well. Manually putting in variables like #_a1 doesn't seem great but I'm not sure there's a better way.

Maybe create some fancy selector classes?

History.where( MapSelector.new(['details','status'], 'good', operator: 'eq') )

[andrykonchin]

Maybe create some fancy selector classes?

Yeah, it's thought-provoking. Thank you!

[andrykonchin]

Fixed in #832