如何在Oracle 19c中利用JSON_OBJECT生成嵌套JSON返回给前端-Oracle-PHP中文网

Oracle 19c中JSON_OBJECT生成嵌套JSON需显式用JSON_OBJECT或JSON_ARRAYAGG构造子对象，键名须为字符串字面量，空值默认忽略，一对多嵌套需子查询聚合，NULL ON NULL保留空键，COALESCE可强制返回空数组，大数据量宜用WITH预聚合并先过滤后聚合。

如何在oracle 19c中利用json_object生成嵌套json返回给前端

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，否则该键直接消失。

Expanso json-flatten

通过 CLI 或 MCP 服务器，使用 Expanso Edge 管道扁平化嵌套 JSON 对象。

下载

处理 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 更省资源。