是否可以有条件地构建一个JSON对象,例如:
mysql> SELECT JSON_OBJECT(
CASE
WHEN some_condition THEN key, val
ELSE
// do nothing
END CASE
);
如果没有,我该如何有条件地构建一个JSON对象。从基础上我只想插入非零属性。
我们可以使用 JSON_MERGE_PATCH
条件合并json对象。
JSON_MERGE_PATCH
忽略具有NULL
值的对象。因此,我们可以在条件失败的任何地方都使用NULL
值构建JSON对象,否则正常创建它们。
架构(mysql v8.0(
查询#1
SELECT JSON_MERGE_PATCH(
-- Following key will exist because condition is success
JSON_OBJECT('key1', CASE WHEN 1 /* some condition here */ THEN 'value1' ELSE NULL END),
-- Following key will not exist because condition fails
JSON_OBJECT('key2', CASE WHEN 0 /* some condition here */ THEN 'value2' ELSE NULL END)
) as result;
结果 |
---|
一个选项是:
mysql> SET @`key` = 'key',
-> @`val` = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT
-> CASE WHEN @`key` IS NOT NULL AND @`val` IS NOT NULL THEN
-> JSON_OBJECT(@`key`, @`val`)
-> END `json`;
+------------+
| json |
+------------+
| {"key": 1} |
+------------+
1 row in set (0.00 sec)
mysql> SET @`key` = 'key',
-> @`val` = NULL;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT
-> CASE WHEN @`key` IS NOT NULL AND @`val` IS NOT NULL THEN
-> JSON_OBJECT(@`key`, @`val`)
-> END `json`;
+------+
| json |
+------+
| NULL |
+------+
1 row in set (0.01 sec)