MySQL 形成JSON_OBJECT，在 NULL 上指定缺席 - 可能吗?

Question

我正在转换我的一些MySQL。 我正在使用JSON_OBJECT和JSON_OBJECTAGG来形成JSON文档。 问题是我有很多 NULL 字段，在这种情况下，我不希望 MySQL 将 NULL 字段添加到 JSON 结构中。我希望此字段不存在。

任何版本的MySQL都可以吗？

扫描互联网，我发现Oracle DB中有类似的东西： https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/JSON_OBJECT.html#GUID-1EF347AE-7FDA-4B41-AFE0-DD5A49E8B370

有ABSENT ON NULL条款。

Answer 1

我刚刚得到了同样的问题。这是我如何解决它：

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

它将空键替换为'null__'，然后将它们从最终对象中删除。因此无需后处理。
JSON_REMOVE将{"null__":1,"b":2}转换为{"b":2}，也{"null__":1}转换为{}，因此最终结果始终是一个对象。
顺便说一句，我使用了'null__'，因为我知道column_holding_property_value永远不会包含该值。

Answer 2

解决方案 1：

这是在mysql中处理此问题的一种方法：

JSON_OBJECTAGG(IFNULL(key, ''), value)

空字段将重新映射到"，防止SQL错误。然后，当您解析结果时，您可以过滤掉空的 ''。

此解决方案取决于是否能够让调用方执行查询后筛选。

解决方案 2：

摆脱这些 null 值的另一种方法是添加 where 子句，无论它们是在对象的键中还是值中。例如：

SELECT JSON_OBJECTAGG(color, red) 
FROM ( SELECT 'color', 'red' UNION select 'color2', 'blue' UNION select 'color3', null ) AS x
WHERE red IS NOT NULL

请注意，如果您的 null 来自左连接，并且您仍然需要 null 字段用于其他目的，则 this thing 可能对您没有帮助。

如果这两种解决方案都不起作用，那么对不起，我对你没有想法！

Answer 3

我相信以下内容可能是没有ABSENT ON NULL的解决方法：

分裂

JSON_OBJECT( key1, value1, key2, value2, ..., keyX, valueX )

转换为多个JSON_OBJECT()函数，并有条件地将它们与JSON_MERGE_PATCH()合并：

JSON_MERGE_PATCH( IF( value1 is null, "{}", JSON_OBJECT( key1, value1 ) ), 
IF( value2 is null, "{}", JSON_OBJECT( key2, value2 ) ), 
..., 
IF( valueX is null, "{}", JSON_OBJECT( keyX, valueX ) )
)

查询示例：

SELECT 
JSON_OBJECT( 'value', q.SEQ,
'isEven', q.IS_EVEN,
'isMultipleOfThree', q.IS_MULTIPLE_OF_3
) AS result
FROM ( SELECT 
SEQ, 
NULLIF( SEQ MOD 2 = 0, FALSE ) AS IS_EVEN, 
NULLIF( SEQ MOD 3 = 0, FALSE ) AS IS_MULTIPLE_OF_3
FROM SEQ_1_TO_6 ) AS q

当转换为

SELECT 
JSON_MERGE_PATCH( JSON_OBJECT( 'value', q.SEQ ),
IF( q.IS_EVEN IS NULL, "{}", JSON_OBJECT( 'isEven', q.IS_EVEN ) ),
IF( q.IS_EVEN IS NULL, "{}", JSON_OBJECT( 'isMultipleOfThree', q.IS_MULTIPLE_OF_3 ) )
) AS result
FROM ( SELECT 
SEQ, 
NULLIF( SEQ MOD 2 = 0, FALSE ) AS IS_EVEN, 
NULLIF( SEQ MOD 3 = 0, FALSE ) AS IS_MULTIPLE_OF_3
FROM SEQ_1_TO_6 ) AS q

将仅显示那些不为空的属性。