PostgreSQL 12中CASE语句用json_object_keys报错的改写方法

错误原因 #

json_object_keys是返回多行结果的集合函数，而CASE表达式要求返回单一值，直接在CASE中调用这类函数会触发该报错。

改写方案 #

使用LEFT JOIN LATERAL关联集合函数的执行结果，替代原CASE逻辑，这样既能正确展开JSON对象的键，也能处理空JSON对象的场景：

假设原查询结构如下：

SELECT 
  id,
  case when(marks <> '{}') then json_object_keys(marks::json) else null end as mark1
FROM student;

改写后的查询：

SELECT 
  s.id,
  j.mark1
FROM student s
LEFT JOIN LATERAL (
  SELECT json_object_keys(s.marks::json) AS mark1
  WHERE s.marks <> '{}'
) j ON true;

如果希望空JSON对象时，每行仍保留一个NULL值（而非无对应行），可以用COALESCE显式处理：

SELECT 
  s.id,
  COALESCE(j.mark1, NULL) AS mark1
FROM student s
LEFT JOIN LATERAL (
  SELECT json_object_keys(s.marks::json) AS mark1
  WHERE s.marks <> '{}'
) j ON true;

说明 #

在PostgreSQL 12中，集合函数不能直接出现在CASE、SELECT列表等期望单一值的位置。LATERAL关联允许将原表每行的字段作为参数传递给集合函数，并将函数返回的多行结果与原行关联，是处理这类场景的标准方式。

内容的提问来源于stack exchange，提问作者code_kmr