我正在Postgres上运行一个令人不安的请求，无法弄清楚以下问题。
注意：以下请求不需要表格，您可以在任何postgres提示符中尝试。
给定以下示例数据：

SELECT
*
FROM jsonb_to_recordset('[{"cat": "A", "key": null, "value": null}, {"cat": "A", "key": "key_A", "value": "val_A"}, {"cat": "B", "key": null, "value": null}, {"cat": "C", "key": "key_C", "value": "val_C"}]')
AS x(cat text, key text, value text);

cat |  key  | value 
-----+-------+-------
 A   |       | 
 A   | key_A | val_A
 B   |       | 
 C   | key_C | val_C
(4 rows)

以下查询工作正常：

SELECT
cat,
CASE WHEN bool_or(key IS NULL) THEN null
ELSE
   jsonb_object_agg(COALESCE(key, '???'), value)
END AS obj
FROM jsonb_to_recordset('[{"cat": "A", "key": null, "value": null}, {"cat": "A", "key": "key_A", "value": "val_A"}, {"cat": "B", "key": null, "value": null}, {"cat": "C", "key": "key_C", "value": "val_C"}]')
AS x(cat text, key text, value text)
GROUP BY cat

cat |        obj         
-----+--------------------
 B   | 
 C   | {"key_C": "val_C"}
 A   | 
(3 rows)

但我不明白为什么我必须使用COALESCE，因为我猜在这一点上值不能为空（正如你可以看到的结果有没有???键。
但是，当我删除COALESCE：

SELECT
cat,
CASE WHEN bool_or(key IS NULL) THEN null
ELSE
   jsonb_object_agg(key, value)
END AS obj
FROM jsonb_to_recordset('[{"cat": "A", "key": null, "value": null}, {"cat": "A", "key": "key_A", "value": "val_A"}, {"cat": "B", "key": null, "value": null}, {"cat": "C", "key": "key_C", "value": "val_C"}]')
AS x(cat text, key text, value text)
GROUP BY cat

psql:commands.sql:9: ERROR:  field name must not be null

我会感激任何 lightning ！干杯。