Bug #108606 How to remove null property using JSON_OBJECT in mysql?

• Submitted: 25 Sep 2022 2:35
• Modified: 26 Sep 2022 12:11
• Reporter: Dimpal Bindas
• Status: Not a Bug
• Category: MySQL Server: JSON
• Severity: S3 (Non-critical)
• OS: Any
• CPU Architecture: Any

[25 Sep 2022 2:35] Dimpal Bindas

Description:
Here is query -
At the time of json generation using JSON_OBJECT function I do not want to create unnecessary fields which are having null value. and standard json does not keep null property or there must be some way to remove those properties which are having null values.

How to repeat:
create temporary table temp_table
(
	Id int null,
    name varchar(30) null
);
insert into temp_table values (1, 'DEMO1');
insert into temp_table values (2, 'DEMO2');
insert into temp_table values (null, 'DEMO3');
insert into temp_table values (null, null);

SELECT JSON_ARRAYAGG(JSON_OBJECT('id', id, 'name', name)) as jsn
from temp_table;

#OUTPUT 
#Actual OUTPUT [{"id": 1, "name": "DEMO1"}, {"id": 2, "name": "DEMO2"}, {"id": null, "name": "DEMO3"}, {"id": null, "name": null}]
#Expected OUTPUT [{"id": 1, "name": "DEMO1"}, {"id": 2, "name": "DEMO2"}, {"name": "DEMO3"}]

[26 Sep 2022 12:11] MySQL Verification Team

Hi Mr. Bindas,

Thank you for your bug report.

However, this is not a bug.

It is a responsibility of the schema and query creator / maintainer to make sure that NULLs do not creep into an JSON object. Hence, you can achieve it easily, for example, by creating a table with non-NULLable column types, for example, with auto-increment types.

If you read our Reference Manual, you will easily conclude that the function that you are using is just aggregating the existing values. It can not replace them by its own volition.

Not a bug.