The way to implement FILTER semantics with Oracle JSON combination features

A cool customary SQL:2003 function is the combination FILTER clause, which is supported natively by no less than these RDBMS:

• ClickHouse
• CockroachDB
• DuckDB
• Firebird
• H2
• HSQLDB
• PostgreSQL
• SQLite
• Trino
• YugabyteDB

The next combination perform computes the variety of rows per group which satifsy the FILTER clause:

SELECT
  COUNT(*) FILTER (WHERE BOOK.TITLE LIKE 'A%'),
  COUNT(*) FILTER (WHERE BOOK.TITLE LIKE 'B%'),
  ...
FROM BOOK

That is helpful for pivot type queries, the place a number of combination values are computed in a single go. For most elementary kinds of combination perform, it may be emulated just by utilizing CASE expressions, as a result of customary combination features ignore NULL values when aggregating. The next is equal to the above, in all RDBMS:

SELECT
  COUNT(CASE WHEN BOOK.TITLE LIKE 'A%' THEN 1 END),
  COUNT(CASE WHEN BOOK.TITLE LIKE 'B%' THEN 1 END),
  ...
FROM BOOK

What if we’re aggregating JSON?

Issues are a bit completely different when aggregating JSON. Take a look at the next instance, the place we don’t need to depend the books, however checklist them in a JSON array, or object:

SELECT
  JSON_ARRAYAGG(BOOK.TITLE)
    FILTER (WHERE BOOK.LANGUAGE_ID = 1),
  JSON_OBJECTAGG('id-' || BOOK.ID, BOOK.TITLE)
    FILTER (WHERE BOOK.LANGUAGE_ID = 2),
  ...
FROM BOOK

Issues are completely different with these assortment combination features, as a result of NULL values are literally fascinating there, so we need to checklist them within the ensuing JSON doc. Assuming there are books with a NULL title, we’d get:

|JSON_ARRAYAGG                |JSON_OBJECTAGG                      |
|-----------------------------|------------------------------------|
|["1984", "Animal Farm", null]|{ "id-4" : "Brida", "id-17" : null }|

This makes emulating the FILTER clause (e.g. on Oracle) a lot tougher, as a result of we can’t simply use ABSENT ON NULL like this:

SELECT
  JSON_ARRAYAGG(
    CASE WHEN T_BOOK.LANGUAGE_ID = 1 THEN T_BOOK.TITLE END 
    ABSENT ON NULL
  ),
  JSON_OBJECTAGG(
    'id-' || T_BOOK.ID, 
    CASE WHEN T_BOOK.LANGUAGE_ID = 2 THEN T_BOOK.TITLE END
    ABSENT ON NULL
  )
FROM T_BOOK;

As a result of now, the reputable null titled books are lacking and we’re getting this as a substitute:

|JSON_ARRAYAGG         |JSON_OBJECTAGG  |
|----------------------|----------------|
|["1984","Animal Farm"]|{"id-4":"Brida"}|

We can’t use NULL ON NULL both, as a result of that might simply flip the FILTER semantics right into a mapping semantics, and produce too many values:

|JSON_ARRAYAGG                        |JSON_OBJECTAGG                                                   |
|-------------------------------------|-----------------------------------------------------------------|
|["1984","Animal Farm",null,null,null]|{"id-1":null,"id-4":"Brida","id-3":null,"id-2":null,"id-17":null}|

E.g. whereas id-3 and id-2 values are NULL as a result of the FILTER emulating CASE expression maps them to NULL, the id-17 worth actually has a NULL title.

Workaround: Wrap information in an array

As a workaround, we are able to:

• Wrap reputable information into an array
• Apply ABSENT ON NULL to take away rows as a result of FILTER emulation
• Unwrap information once more from the array

For the unwrapping, we’re going to be utilizing JSON_TRANSFORM:

SELECT
  JSON_TRANSFORM(
    JSON_ARRAYAGG(
      CASE 
        WHEN T_BOOK.LANGUAGE_ID = 1 

        -- Wrap reputable information into an array, together with nulls
        THEN JSON_ARRAY(T_BOOK.TITLE NULL ON NULL)
      END 

      -- Take away NULLs as a result of FILTER emulation
      ABSENT ON NULL
    ),

    -- Unwrap information achieve from the array
    NESTED PATH '$[*]' (REPLACE '@' = PATH '@[0]')
  ),

  JSON_TRANSFORM(
    JSON_OBJECTAGG(
      'id-' || T_BOOK.ID, 
      CASE 
        WHEN T_BOOK.LANGUAGE_ID = 2 

        -- Wrap reputable information into an array, together with nulls
        THEN JSON_ARRAY(T_BOOK.TITLE NULL ON NULL)
      END

      -- Take away NULLs as a result of FILTER emulation
      ABSENT ON NULL
    ),

    -- Unwrap information achieve from the array
    NESTED PATH '$.*' (REPLACE '@' = PATH '@[0]')
  )
FROM T_BOOK;

jOOQ assist

jOOQ 3.20 will implement the above emulations for:

This fashion, you’ll be able to proceed to transparently use FILTER on any combination perform, additionally in Oracle.