A method I’ve used before is:
PluginStoreRow.where("value::json->>'provider'=?", provider)
There is no need for the database column to actually be set to json for that to work. (see here).
The other thing I’ve used to help with JSON in plugins is ActiveRecord::Store. It allows storing data in json, while keeping all the activerecord validation/serialization magic.
https://github.com/discourse/discourse-chat-integration/blob/master/app/models/channel.rb#L3
Switching to jsonb would certainly be more performant for looking up values - I like the idea of adding an additional column, so that we don’t break existing plugins. If we add jsonb, it looks like activerecord has good support for it as well 