jsonb special query operators

[jwoertink]

We can now map to jsonb fields using JSON::Any in our migrations and columns, but currently querying on those fields is limited to normal WHERE syntax, and some hefty crystal:

# WHERE users.preferences = {"theme": "dark_mode"}
UserQuery.new.preferences(JSON::Any.new({"theme" => JSON::Any.new("dark_mode")}))

Postgres supports several different operators for doing special queries on JSON https://www.postgresql.org/docs/current/functions-json.html

We need some type safe ways to do things like "Give me all users that have dark_mode as their theme preference" and such.

[jwoertink]

To along with this, I just ran in to an issue with rails. If you want to check that a jsonb column has a specific key, postgres uses column ? 'key'. Since the ? is the same as the bind args, you can't pass that key as a separate argument. You'd have to use string interpolation if you needed that to be dynamic, but obviously that opens the door to some issues.

User.where("tags ? 'active'").count #=> 100
User.where("tags ? '?'", "active").count #=> Wrong number of bind args exception

For Avram, it would be super helpful to have a method that does this for us, but in a type safe and secure way.

# This would be nice. Or something along these lines.
UserQuery.new.tags.has_key("active").select_count

[jkthorne]

I like the idea of using method missing to create query methods.

UserQuery.new.tags.active_key("active").select_count

This would be a little bit of magic but I think it would fit into the API well.

[hovsater]

I'd vote against using method_missing. While convenient, I really like the simpleness/explicitness of Lucky in general. There's usually not much "magic" going on. 🙂

[jwoertink]

Agreed. I like the suggestion @wontruefree, but there was some talk floating around of method missing going away at some point. Plus, we want to avoid random bits of "magic". I think we may be able to even get a bit more type-safe with this if #695 gets approved. In the case of the example, active would be a property on the object which may allow us to pull that in from the macro level (maybe with an annotation?).

Just half-baked, but something like:

struct UserTagsJSON
  include JSON::Serializable
  property active : Bool
end

# We know tags would be a serialized object
# If we pass a block, then send the serialized instance down
# and build a query from that...
UserQuery.new.tags(&.active(true)).select_count

No clue how that'd work, but might be kinda neat and definitely unique.