23ai 新特性之JSON-关系双栖

Oracle 23ai的JSON-关系双栖，核心是通过“JSON-关系双栖视图”（Duality View）实现存储格式与访问格式的解耦——数据底层仍以规范化的关系表形式存储，享受关系型模型的高效、一致与低冗余优势；同时，通过双栖视图将关系数据自动组装为JSON文档，让应用程序可以通过JSON语法、REST API等方式便捷访问，无需复杂的对象-关系映射（ORM）层。
作为原生特性，无需额外部署第三方工具，只需创建双栖视图，即可实现关系数据与JSON文档的双向交互。

1. 环境准备与底层表创建

首先创建电商订单相关的底层关系表，包含订单表、订单项表、客户表，用于存储规范化的订单数据：

-- 创建客户表
CREATE TABLE customer (
    cust_id NUMBER PRIMARY KEY,
    cust_name VARCHAR2(100) NOT NULL,
    phone VARCHAR2(20),
    email VARCHAR2(100)
);

-- 创建订单表
CREATE TABLE orders (
    order_id NUMBER PRIMARY KEY,
    cust_id NUMBER REFERENCES customer(cust_id),
    order_date DATE DEFAULT SYSDATE,
    total_amount NUMBER(10,2) NOT NULL,
    order_status VARCHAR2(20) NOT NULL
);

-- 创建订单项表
CREATE TABLE order_item (
    item_id NUMBER PRIMARY KEY,
    order_id NUMBER REFERENCES orders(order_id),
    product_name VARCHAR2(100) NOT NULL,
    quantity NUMBER NOT NULL,
    unit_price NUMBER(10,2) NOT NULL
);

-- 插入测试数据
INSERT INTO customer VALUES (1, '张三', '13800138000', 'zhangsan@example.com');
INSERT INTO orders VALUES (1001, 1, SYSDATE, 599.00, '已付款');
INSERT INTO order_item VALUES (10001, 1001, '手机', 1, 499.00);
INSERT INTO order_item VALUES (10002, 1001, '手机壳', 1, 100.00);
COMMIT;

2. 创建JSON-关系双栖视图

使用类GraphQL语法创建双栖视图，将客户、订单、订单项的关联数据组装为层级JSON文档：

-- 创建JSON-关系双栖视图，组装订单完整信息JSON
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW order_duality_view AS
  SELECT 
    o.order_id AS "orderId",
    o.order_date AS "orderDate",
    o.total_amount AS "totalAmount",
    o.order_status AS "orderStatus",
    c.cust_id AS "customer.custId",
    c.cust_name AS "customer.custName",
    c.phone AS "customer.phone",
    COLLECTION(
      SELECT 
        oi.item_id AS "itemId",
        oi.product_name AS "productName",
        oi.quantity AS "quantity",
        oi.unit_price AS "unitPrice"
      FROM order_item oi
      WHERE oi.order_id = o.order_id
    ) AS "orderItems"
  FROM orders o
  JOIN customer c ON o.cust_id = c.cust_id;

3. 数据访问与操作实战

创建双栖视图后，即可通过多种方式访问和操作数据，实现关系数据与JSON文档的无缝交互。

• 场景1：以JSON格式查询订单数据

  -- 通过双栖视图查询JSON格式的订单数据
  SELECT JSON_VALUE(data, '$.orderId') AS order_id,
     JSON_VALUE(data, '$.customer.custName') AS cust_name,
     JSON_VALUE(data, '$.totalAmount') AS total_amount
  FROM order_duality_view;
  -- 查询完整JSON文档
  SELECT data FROM order_duality_view WHERE JSON_VALUE(data, '$.orderId') = 1001;

  执行后，会返回包含客户信息、订单项的层级JSON文档，无需手动关联多表，直接适配前端应用的JSON数据需求。

• 场景2：通过JSON格式更新数据

  
  -- 通过双栖视图更新JSON文档，同步更新底层关系表
  UPDATE order_duality_view
  SET data = JSON_MERGEPATCH(data, '{"orderStatus": "已发货", "totalAmount": 699.00}')
  WHERE JSON_VALUE(data, '$.orderId') = 1001;
  COMMIT;
  -- 验证底层关系表数据是否同步更新
  SELECT order_status, total_amount FROM orders WHERE order_id = 1001;
  更新JSON文档后，底层orders表中的order_status和total_amount字段会自动同步更新，确保数据一致性。

• 场景3：通过REST API访问JSON数据
  Oracle 23ai支持通过REST API访问双栖视图的JSON数据，无需额外开发接口，直接配置REST端点即可：

  -- 启用REST服务
  BEGIN
  DBMS_REST.SET_ENABLED(TRUE);
  END;
  /
  -- 为双栖视图创建REST端点
  BEGIN
  DBMS_REST.CREATE_RESOURCE(
  resource_path => '/order-api',
  source_type => 'VIEW',
  source_name => 'ORDER_DUALITY_VIEW',
  method => 'GET'
  );
  END;
  /

配置完成后，即可通过HTTP请求访问JSON数据，例如：GET http://localhost:8080/ords/rag_user/order-api?orderId=1001，返回JSON格式的订单详情。