Oracle 19c中JSON_OBJECT生成嵌套JSON需显式用JSON_OBJECT或JSON_ARRAYAGG构造子对象，键名须为字符串字面量，空值默认忽略，一对多嵌套需子查询聚合，NULL ON NULL保留空键，COALESCE可强制返回空数组，大数据量宜用WITH预聚合并先过滤后聚合。
JSON_OBJECT 生成嵌套 JSON 的基本写法
Oracle 19c 原生支持 JSON_OBJECT，但默认是扁平结构；要嵌套，必须把子对象作为表达式传入，不能靠字段名自动推导。关键点在于：子对象必须用另一个 JSON_OBJECT 或 JSON_ARRAYAGG 构造，并显式指定为键的值。
常见错误是直接写 JSON_OBJECT('user', t.user_name, 'orders', t.orders)——如果 t.orders 是个表连接结果集，这会报错“ORA-40442: JSON_OBJECT contains no key/value pairs”，因为标量列无法自动转成 JSON 对象。
- 嵌套对象必须由
JSON_OBJECT或JSON_ARRAYAGG显式构造 - 键名必须是字符串字面量或
KEY子句，不能是列别名 - 空值默认被忽略，如需保留需加
NULL ON NULL
用 JSON_OBJECT + JSON_ARRAYAGG 实现一对多嵌套
典型场景：查用户及其订单列表。必须用子查询或连接 + JSON_ARRAYAGG 聚合子记录，再把聚合结果塞进外层 JSON_OBJECT。
SELECT JSON_OBJECT(
'id' VALUE u.id,
'name' VALUE u.name,
'orders' VALUE (
SELECT JSON_ARRAYAGG(
JSON_OBJECT(
'order_id' VALUE o.order_id,
'amount' VALUE o.amount,
'items' VALUE (
SELECT JSON_ARRAYAGG(
JSON_OBJECT('sku' VALUE i.sku, 'qty' VALUE i.qty)
)
FROM order_items i
WHERE i.order_id = o.order_id
) NULL ON NULL
)
)
FROM orders o
WHERE o.user_id = u.id
) NULL ON NULL
) AS result
FROM users u
WHERE u.id = 123;
注意：内层 JSON_ARRAYAGG 必须有 WHERE 关联条件，否则会笛卡尔积；若子查询无结果，JSON_ARRAYAGG 返回 NULL，外层加 NULL ON NULL 才会输出 "orders": null，否则该键直接消失。
立即学习“前端免费学习笔记（深入）”；
处理 NULL、空数组和性能陷阱
JSON_OBJECT 默认跳过 NULL 值，前端可能因此缺失字段；而 JSON_ARRAYAGG 在无数据时返回 NULL，不是空数组 []——这点极易导致前端解析失败。
- 强制保留空数组：
COALESCE(JSON_ARRAYAGG(...), JSON_ARRAY()) - 避免 N+1 查询：嵌套两层以上（如用户→订单→商品→库存）时，用
LEFT JOIN+ 窗口函数或提前聚合，别在每层都写相关子查询 - 大结果集慎用：每个
JSON_OBJECT调用都有开销，10 万行数据生成 JSON 可能比纯 SQL 慢 3–5 倍 - 字符集注意：确保数据库字符集支持 UTF-8（如
AL32UTF8），否则中文变乱码
替代方案：用 WITH 子句预聚合提升可读性与维护性
当嵌套超过两层或逻辑复杂时，硬写嵌套子查询极难调试。用 WITH 把各层 JSON 提前算好，主查询只做拼装，既清晰又方便复用。
WITH user_orders AS (
SELECT
u.id,
u.name,
JSON_ARRAYAGG(
JSON_OBJECT(
'order_id' VALUE o.order_id,
'items' VALUE COALESCE(
(SELECT JSON_ARRAYAGG(JSON_OBJECT('sku' VALUE i.sku))
FROM order_items i WHERE i.order_id = o.order_id),
JSON_ARRAY()
)
)
) AS orders_json
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name
)
SELECT JSON_OBJECT(
'user' VALUE JSON_OBJECT('id' VALUE id, 'name' VALUE name),
'data' VALUE orders_json
) AS payload
FROM user_orders;
这种写法让每一层职责单一，也便于加 HINT 或拆分测试。真正容易被忽略的是：Oracle 对 CTE 中 JSON 函数的优化有限，JSON_ARRAYAGG 仍会在内存中构建完整中间结果——数据量大时，先过滤再聚合，比全量聚合后 WHERE 更省资源。
