Emulate JSON_OBJECT's ABSENT ON NULL clause in MySQL

[lukaseder]

An interesting solution to implementing ABSENT ON NULL support in MySQL is described here:
https://stackoverflow.com/a/69907488/521799

For example:

WITH j (o) AS (
  SELECT json_object('a', 'a', 'b', null, 'c', 'c', 'd', null)
)
SELECT 
  j.o, (
    SELECT JSON_OBJECTAGG(k, JSON_EXTRACT(j.o, CONCAT('$."', jt.k, '"')))
    FROM JSON_TABLE(
      JSON_KEYS(o), '$[*]'
      COLUMNS (
        k VARCHAR(200) PATH '$'
      )
    ) jt
    WHERE JSON_EXTRACT(j.o, CONCAT('$."', jt.k, '"')) != CAST('null' AS JSON)
  ) removed
FROM j;

The result being:

|o                                         |removed             |
|------------------------------------------|--------------------|
|{"a": "a", "b": null, "c": "c", "d": null}|{"a": "a", "c": "c"}|

(It works on MySQL only, in this form. There's probably a MariaDB version)

[lukaseder]

Looking into this now for MySQL (let's ignore MariaDB for now). The emulation requires support for correlated derived tables, which has been implemented in some MySQL 8 version only. MariaDB doesn't support it.

[lukaseder]

The actual query is this one:

-- Original
select json_object('a', null absent on null)

-- Emulated, on MySQL
select (
  select coalesce( -- Coalesce is essential
    json_objectagg(`jt`.`k`, json_extract(
      `j`.`o`,
      concat('$."', `jt`.`k`, '"')
    )), 
    json_object()
  )
  from -- Working with derived tables, to avoid trouble
    (
      select json_object('a', null) as `o` -- original `JSON_OBJECT()` constructor here
    ) as `j`,
    json_table(
      json_keys(`j`.`o`),
      '$[*]'
      columns (`k` text path '$')
    ) as `jt`
  where json_extract(
    `j`.`o`,
    concat('$."', `jt`.`k`, '"')
  ) <> cast('null' as json)
);

[lukaseder]

The JSON_KEYS() function used here is probably worth supporting on its own: #14046