Add Native JSONB Query Support for PostgreSQL

[ffroliva]

Description / Use Case
Currently, SpringFilter does not provide any out-of-the-box support for querying JSON/JSONB columns in PostgreSQL. As more applications rely on structured or semi-structured data stored in JSONB fields, having built-in capabilities to filter by nested JSON properties (e.g., jsonField->>'myKey') would greatly enhance the flexibility and usability of this library.

Why It Matters

• JSON/JSONB fields in PostgreSQL are common in modern applications.
• Developers often need to filter data based on values nested inside these JSON documents.
• Relying on custom specifications or native queries works but requires a fair amount of manual setup.
• Providing a native way to parse JSON-specific operators (like ->> or jsonb_extract_path_text) or to split property paths (e.g., myJsonField.innerKey) would make the library more complete and developer-friendly.

Proposed Enhancement

1. Introduce a JSON/JSONB Property Resolver

   • A pluggable resolver that detects when the requested field is a JSON/JSONB column (for example, by matching myJsonField.someNestedKey).
   • Internally uses CriteriaBuilder.function("jsonb_extract_path_text", ...) or equivalent to retrieve nested JSON values as a string (or cast to numeric types if needed).

2. Extended Operators & Syntax (Optional / Future)

   • Consider supporting more complex JSON operators (e.g., @>, ?|, ?&) as additional filter operators if there's enough demand.
   • This could be integrated into the existing ComparisonNode or introduced as a separate operator category.

3. Backwards Compatibility

   • Keep it optional/configurable so applications that do not use JSONB can remain unaffected.
   • Provide clear documentation or examples showcasing how to enable JSONB filters.

Example of Potential Usage

GET /entities?filter=myJsonField.key1=='value'

internally resolved as:

cb.function("jsonb_extract_path_text", String.class, root.get("myJsonField"), cb.literal("key1"))

then compared to "value".

Benefits

• Simplifies queries involving JSON/JSONB columns.
• Maintains SpringFilter’s declarative style for filters, avoiding manually written native SQL or complex specifications.
• Encourages best practices for storing and querying semi-structured data within the framework’s existing architecture.

Additional Context
A few users have expressed interest in a built-in solution for JSON/JSONB. This feature would align with modern database usage trends and make SpringFilter more robust and appealing for a wider range of applications.

---

Thank you for considering this enhancement! If there is any interest in proceeding, I’d be happy to help discuss or refine the approach.