Methods to implement FILTER semantics with Oracle JSON mixture features

A cool commonplace SQL:2003 function is the mixture FILTER clause, which is supported natively by at the least these RDBMS:

ClickHouse
CockroachDB
DuckDB
Firebird
H2
HSQLDB
PostgreSQL
SQLite
Trino
YugabyteDB

The next mixture 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 model queries, the place a number of mixture values are computed in a single go. For most elementary sorts of mixture perform, it may be emulated just by utilizing CASE expressions, as a result of commonplace mixture 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. Have a look at the next instance, the place we don’t wish to rely the books, however record 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 mixture features, as a result of NULL values are literally attention-grabbing there, so we wish to record 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 not 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 reliable null titled books are lacking and we’re getting this as an alternative:

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

We can not use NULL ON NULL both, as a result of that will 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 reliable information into an array
Apply ABSENT ON NULL to take away rows because of the 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 reliable information into an array, together with nulls
        THEN JSON_ARRAY(T_BOOK.TITLE NULL ON NULL)
      END 

      -- Take away NULLs resulting from 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 reliable information into an array, together with nulls
        THEN JSON_ARRAY(T_BOOK.TITLE NULL ON NULL)
      END

      -- Take away NULLs resulting from FILTER emulation
      ABSENT ON NULL
    ),

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