[Feature Request] Support range queries on JSONB column keys

[HodaSalim]

Is there an existing issue for this?

• I have searched the existing issues

Is your feature request related to a problem? Please describe

Problem

Sift supports range syntax (10...100) for :int, :decimal, :boolean,
:date, :time, and :datetime filters, and supports key-value filtering on
:jsonb columns — but the two don't compose. There is no way to express a
range filter against a key stored inside a JSONB column.

For example, given a Product model with a metadata JSONB column storing
{ "price": 42 }, today the only options are:

• Exact match on the key: ?filters[metadata]={"price":"42"}
• Range on a real column: ?filters[price]=10...100

There is no syntax that means "records where metadata->>'price' is between 10
and 100."

Current behavior

Sift::Parameter#supports_ranges? returns true for :jsonb (the exclusion
list only contains :string, :text, and :scope), so ValueParser will
construct a Range object from a jsonb value containing .... However,
WhereHandler#apply_jsonb_conditions only branches on Array vs Hash and has no
range path — a Range falls through to value.each do |key, val|, which
iterates the string range via String#succ and produces a chain of nonsensical
WHERE metadata->>'<succ_string>' = '' clauses. The query is valid SQL but
returns no useful results and can be expensive for wide ranges. This is a
latent behavior bug separate from the feature request.

See lib/sift/parameter.rb:31 and lib/sift/where_handler.rb:17-34.

Describe the feature you'd like to see implemented

Proposal

Add first-class support for range queries on JSONB keys. The feature needs
three pieces:

1. A way to address a key inside a JSONB column from the query string.
   Possible syntaxes (no strong preference, listing for discussion):

   • Dot-path: ?filters[metadata.price]=10...100
   • Nested params: ?filters[metadata][price]=10...100
   • Extend the existing JSON form: ?filters[metadata]={"price":"10...100"}

2. A way for the controller to declare the type of each key so Sift can
   cast the JSONB-extracted value safely. Strawman:

   filter_on :metadata, type: :jsonb, keys: {
     price: :decimal,
     released_at: :datetime
   }

3. SQL generation that casts the extracted value and applies the range,
   e.g. (metadata->>'price')::numeric BETWEEN 10 AND 100. Cast must be driven
   by the declared key type; no inference from the value.

Acceptance criteria

• Range syntax (a...b) works against declared JSONB keys for at least
  :int, :decimal, :date, :datetime key types.
• Existing JSONB filters (array containment, key equality, null handling)
  continue to work unchanged.
• Invalid key types or missing key declarations produce a clear validation
  error, not broken SQL.
• The latent range-on-jsonb behavior bug described above is either fixed or
  explicitly rejected by validation.

Open questions

• Which query-string syntax do maintainers prefer? Dot-path is the most
  ergonomic but reserves . as a special char in filter names.
• Should key type declarations be required, or default to :string?
• Should BETWEEN boundaries be inclusive on both ends (matching the existing
  Rails Range .. semantics), or should we follow Sift's ... literal
  string and make the upper bound exclusive?

Out of scope

• Half-open ranges (10... / ...100). Sift doesn't support these for regular
  columns; can be a follow-up.
• Range queries on JSONB array elements.

Describe alternatives you've considered

No response

Additional context

No response