MySQL - 有条件地构建 JSON_OBJECT

Question

是否可以有条件地构建一个 json 对象，例如:

mysql> SELECT JSON_OBJECT(
      CASE
        WHEN some_condition THEN key, val
        ELSE
           // do nothing 
        END CASE

    );

如果没有，我如何有条件地构建一个 json 对象。基本上我只想包含非空属性。

Answer 1

一个选项是:

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)