Oracle 23ai still doesn't support the standard SQL FILTER clause on aggregate functions, which can prove to be tricky to emulate on JSON aggregate functions

jOOQ is a Java library that provides a fluent API for building type-safe SQL queries and interacting with relational databases. With jOOQ, developers can write SQL queries in Java code, leveraging compile-time type checking and code generation to ensure correctness and maintainability. Developers can learn how to use jOOQ to simplify database interactions, reduce SQL boilerplate, and enhance productivity when working with SQL databases in Java applications.

JOOQ

Learn how to implement FILTER semantics with Oracle JSON aggregate functions and the workaround to handle NULL values in the result.

How to implement FILTER semantics with Oracle JSON aggregate functions