MySQL forming JSON_OBJECT, specify absence on NULL - is it possible?

Question

I am converting some of my MySQL. I am using JSON_OBJECT and JSON_OBJECTAGG to form the JSON documents. The problem is that I've got many NULL fields, in which case I don't want MySQL to add NULL field to JSON structure. I want this field to be not present.

Is it possible with any version of MySQL?

Scanning the internet, I've found there is something like that in Oracle DB: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/JSON_OBJECT.html#GUID-1EF347AE-7FDA-4B41-AFE0-DD5A49E8B370

There is ABSENT ON NULL clause.

Answer 1

I just got the same question. Here is how I solved it :

select ...,  
JSON_REMOVE(JSON_OBJECTAGG(IFNULL(column_holding_property_name, 'null__'), column_holding_property_value), '$.null__') as result_column_name  
from ... group by ...;  

It replaces the null keys with 'null__', then removes them from the final object. So no post-processing is needed.
The JSON_REMOVE converts {"null__":1,"b":2} into {"b":2} and also {"null__":1} into {}, so the final result is always an object.
BTW, I used 'null__' because I know that column_holding_property_value will never contain that value.