Oracle Apex触发器处理嵌套JSON数组报错及技术咨询

编译错误原因 #

ORA-00904: "J"."PRICE": 无效标识符，是因为json_table查询时未指定别名j，导致无法识别j.quantity、j.price这类引用。只需给json_table添加别名j即可解决该编译错误。

技术问题逐一解答 #

• 问题1：ORDER_ITEMS_LOCAL表的order_id作为ORDERS_LOCAL表的外键，使用:new.order_id关联是否正确？
  正确。ORDERS_LOCAL插入时，:new.order_id是当前插入订单的主键值，用它作为ORDER_ITEMS_LOCAL的外键，能准确关联到对应订单，完全符合外键关联逻辑。

• 问题2：ORDER_ITEMS_LOCAL表的line_id需自动生成（无JSON响应数据），使用seq_line_id.nextval是否正确？
  正确。由于JSON响应中没有line_id字段，使用序列的nextval可以确保每次插入生成唯一的line_id，满足自动生成的要求。如果使用Oracle 12c及以上版本，也可以考虑用identity column（自增列）替代序列，实现更简洁的自动生成逻辑。

• 问题3：代码中的j（如j.quantity、j.price）指代什么？
  j是json_table的别名，用来指代从JSON数组解析出来的每一行关系型数据。原代码未给json_table定义该别名，因此编译报错，只需在json_table末尾添加j（如from json_table(...) j）即可。

• 问题4：:new.order_items是否能正确获取JSON响应中的order_items数组？
  只要ORDERS_LOCAL表的order_items字段是JSON类型，或是存储合法JSON格式的字符串类型（VARCHAR2/CLOB），:new.order_items就能正确获取对应的JSON数组。如果是字符串类型，需确保存储内容是合法JSON，避免解析失败。

• 问题5：'$[*]'的含义是否为从当前对象开始遍历数组？
  正确。这是JSON路径表达式，$代表JSON根对象，[*]表示遍历根对象下order_items数组的所有元素，将每个数组元素转换为一行关系型数据。

• 问题6：是否需要在json_table的columns中加入order_id？
  不需要。order_id来自ORDERS_LOCAL表的:new.order_id，并非JSON数组中的字段，直接在SELECT列表中引用:new.order_id即可，无需在json_table的columns里定义。

修正后的触发器代码 #

create or replace trigger "TR_MAINTAIN_LINES"
AFTER
insert or update or delete on "ORDERS_LOCAL"
for each row
begin
    if inserting then
        insert into ORDER_ITEMS_LOCAL ( order_id, line_id, line_number, product_id, quantity, price) 
        ( select :new.order_id,
                 seq_line_id.nextval,
                 j.line_number,
                 j.product_id,
                 j.quantity,
                 j.price
            from json_table( 
                     :new.order_items,
                     '$[*]' columns (
                         line_number number path '$.line_number',
                         product_id  number path '$.product_id',
                         quantity number        path '$.quantity',
                         price    number        path '$.price' ) ) j ); -- 添加别名j
    elsif deleting then
        delete ORDER_ITEMS_LOCAL
        where order_id = :old.order_id;
    elsif updating then
        delete ORDER_ITEMS_LOCAL
        where order_id = :old.order_id;
        -- 处理更新：删除原有行后重新插入新的订单明细
        insert into ORDER_ITEMS_LOCAL ( order_id, line_id, line_number, product_id, quantity, price) 
        ( select :new.order_id,
                 seq_line_id.nextval,
                 j.line_number,
                 j.product_id,
                 j.quantity,
                 j.price
            from json_table( 
                     :new.order_items,
                     '$[*]' columns (
                         line_number number path '$.line_number',
                         product_id  number path '$.product_id',
                         quantity number        path '$.quantity',
                         price    number        path '$.price' ) ) j );
    end if;
end;
/

注：原代码中json_table定义的line_id for ordinality是数组元素的序号（从1开始），与需要自动生成的LINE_ID无关，因此予以删除，避免字段混淆。

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