如何在Oracle APEX 20.1中提取嵌套JSON对象的PL/SQL值

针对你提供的员工JSON，不管是嵌套的address对象，还是数组categories里的options对象，都可以通过指定层级路径或遍历数组索引的方式，用APEX_JSON包提取值。以下是具体实现示例：

1. 提取嵌套address对象的属性值 #

直接通过.串联层级路径即可，逻辑和提取一级属性一致：

DECLARE
  v_street       VARCHAR2(100);
  v_city         VARCHAR2(50);
  v_state        VARCHAR2(50);
  v_state_short  VARCHAR2(2);
  v_zip_code     NUMBER;
  v_country      VARCHAR2(20);
BEGIN
  -- 解析目标JSON（若JSON来自变量/表字段，替换p_source参数即可）
  APEX_JSON.parse(p_source => '{"employee":{"firstName":"John","lastName":"Smith","email":"jsmith@test.com","phoneNumber":"‎(907) 247-8888","address":{"street":"1527 Pond Reef Rd","city":"Ketchikan","state":"ALaska","stateShort":"AK","zipCode":99901,"country":"USA"},"categories":[{"categoryID":1,"categoryName":"Example1","options":{"ind_1":1,"ind_2":0,"ind_3":0}},{"categoryID":2,"categoryName":"Example","options":{"ind_1":1,"ind_2":1,"ind_3":1}}]}}');
  
  -- 提取address下的各属性值
  v_street := APEX_JSON.get_varchar2(p_path => 'employee.address.street');
  v_city := APEX_JSON.get_varchar2(p_path => 'employee.address.city');
  v_state := APEX_JSON.get_varchar2(p_path => 'employee.address.state');
  v_state_short := APEX_JSON.get_varchar2(p_path => 'employee.address.stateShort');
  v_zip_code := APEX_JSON.get_number(p_path => 'employee.address.zipCode');
  v_country := APEX_JSON.get_varchar2(p_path => 'employee.address.country');
  
  -- 可选：输出验证结果
  DBMS_OUTPUT.PUT_LINE('街道: ' || v_street);
END;
/

2. 提取categories数组中options对象的属性值 #

数组元素需要通过从0开始的索引指定位置，再串联后续对象路径，分两种场景处理：

方式一：直接指定索引提取单条数据 #

如果明确数组元素的位置，直接在路径中写索引即可：

DECLARE
  v_cat1_ind1  NUMBER;
  v_cat2_ind2  NUMBER;
BEGIN
  APEX_JSON.parse(p_source => '{"employee":{"firstName":"John","lastName":"Smith","email":"jsmith@test.com","phoneNumber":"‎(907) 247-8888","address":{"street":"1527 Pond Reef Rd","city":"Ketchikan","state":"ALaska","stateShort":"AK","zipCode":99901,"country":"USA"},"categories":[{"categoryID":1,"categoryName":"Example1","options":{"ind_1":1,"ind_2":0,"ind_3":0}},{"categoryID":2,"categoryName":"Example","options":{"ind_1":1,"ind_2":1,"ind_3":1}}]}}');
  
  -- 提取第一个categories元素的options.ind_1
  v_cat1_ind1 := APEX_JSON.get_number(p_path => 'employee.categories[0].options.ind_1');
  -- 提取第二个categories元素的options.ind_2
  v_cat2_ind2 := APEX_JSON.get_number(p_path => 'employee.categories[1].options.ind_2');
  
  DBMS_OUTPUT.PUT_LINE('分类1 Ind1值: ' || v_cat1_ind1);
  DBMS_OUTPUT.PUT_LINE('分类2 Ind2值: ' || v_cat2_ind2);
END;
/

方式二：遍历数组所有元素（适配不确定长度的场景） #

如果categories数组长度不固定，先用APEX_JSON.get_count获取数组长度，再循环遍历所有元素：

DECLARE
  v_cat_count  NUMBER;
  v_cat_id     NUMBER;
  v_ind1       NUMBER;
BEGIN
  APEX_JSON.parse(p_source => '{"employee":{"firstName":"John","lastName":"Smith","email":"jsmith@test.com","phoneNumber":"‎(907) 247-8888","address":{"street":"1527 Pond Reef Rd","city":"Ketchikan","state":"ALaska","stateShort":"AK","zipCode":99901,"country":"USA"},"categories":[{"categoryID":1,"categoryName":"Example1","options":{"ind_1":1,"ind_2":0,"ind_3":0}},{"categoryID":2,"categoryName":"Example","options":{"ind_1":1,"ind_2":1,"ind_3":1}}]}}');
  
  -- 获取categories数组的元素总数
  v_cat_count := APEX_JSON.get_count(p_path => 'employee.categories');
  
  -- 循环遍历每个数组元素
  FOR i IN 0 .. v_cat_count - 1 LOOP
    v_cat_id := APEX_JSON.get_number(p_path => 'employee.categories[' || i || '].categoryID');
    v_ind1 := APEX_JSON.get_number(p_path => 'employee.categories[' || i || '].options.ind_1');
    
    DBMS_OUTPUT.PUT_LINE('分类ID: ' || v_cat_id || ', Ind1值: ' || v_ind1);
  END LOOP;
END;
/

关键注意点 #

• APEX_JSON的数组索引从0开始，不是常规的1起始；
• 根据属性值类型选择对应get方法：字符串用get_varchar2，数字用get_number；
• 若JSON已通过其他方式解析（比如从APEX集合或表字段加载），可省略APEX_JSON.parse步骤，直接用路径提取。

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