Oracle生成JSON时，如何优雅隐藏含空值的整个对象？

要实现左连接的tab_2无匹配数据时隐藏整个tab_2对象，Oracle有直接且简洁的方案，核心是利用JSON_OBJECT的ABSENT ON NULL参数，结合聚合函数处理一对多关联场景。

修正后的查询语句 #

针对你的表结构（tab_1与tab_2为一对多关联），正确查询如下：

SELECT
  JSON_OBJECT(
    'tab_1_a' VALUE tab_1.col_a,
    'tab_1_b' VALUE tab_1.col_b,
    'tab_2' VALUE JSON_ARRAYAGG(
                    JSON_OBJECT('c' VALUE tab_2.col_c, 'd' VALUE tab_2.col_d)
                  )
    ABSENT ON NULL  -- 当tab_2对应值为NULL时，不生成该键值对
  ) AS result_json
FROM tab_1
LEFT JOIN tab_2 ON tab_1.id = tab_2.fk_id
GROUP BY tab_1.id, tab_1.col_a, tab_1.col_b;

关键逻辑说明 #

1. ABSENT ON NULL参数：Oracle 12c及以上版本支持，当指定键的值为NULL时，整个键值对不会出现在最终JSON中，直接实现空对象隐藏需求。
2. JSON_ARRAYAGG聚合：处理一对多关联，将同一tab_1对应的多个tab_2对象聚合成JSON数组；若无匹配tab_2数据，该函数返回NULL，触发ABSENT ON NULL隐藏tab_2键。
3. 标准左连接语法：替换旧式(+)语法，让关联逻辑更清晰易懂。

结果验证 #

• 当tab_1.id=1时，输出符合期望：

{
  "tab_1_a": "a1",
  "tab_1_b": "b1"
}

• 当tab_1.id=2时，生成包含两个tab_2对象的数组：

{
  "tab_1_a": "a2",
  "tab_1_b": "b2",
  "tab_2": [
    {"c": "c1", "d": "d1"},
    {"c": "c2", "d": "d2"}
  ]
}

一对一关联场景的简化写法 #

若tab_1与tab_2为一对一关联，无需聚合，可直接用CASE判断配合ABSENT ON NULL：

SELECT
  JSON_OBJECT(
    'tab_1_a' VALUE tab_1.col_a,
    'tab_1_b' VALUE tab_1.col_b,
    'tab_2' VALUE CASE WHEN tab_2.fk_id IS NOT NULL THEN
                    JSON_OBJECT('c' VALUE tab_2.col_c, 'd' VALUE tab_2.col_d)
                  END
    ABSENT ON NULL
  ) AS result_json
FROM tab_1
LEFT JOIN tab_2 ON tab_1.id = tab_2.fk_id;

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