我有一个JSON数据存储在CLOB中的表。我们从外部源获得这些数据，最近他们更改了一些格式，这导致了我们的后期处理问题。
该数据包含一个对象，该对象包含用户角色以及与单个角色关联的位置数组（如果格式正确）。
当一个用户的角色（测试角色1）的位置仅包含大括号{}，而另一个用户的角色（测试角色1）也相同，其位置包含有效数据时，就会出现问题。当我们查询第二个用户的角色时，我们得到该用户的空位置日期。
在示例数据中，employeeID 1（Whitbuckle，Dalongrirlum）的角色为"测试角色1"和"测试角色2"，每个角色都有一个Location {}，employeeID 2（Longblade，Skolout）的角色为"测试角色1"，具有有效的位置。另外两个用户的EntitlementJSON属性为空，或者测试角色3具有有效的位置数据。
当我们查询数据时，employeeID 2记录具有空角色，即使我们仅显式选择其employeeID。

• • 请求的解决方案：**我正在编写一个验证过程，以确保识别格式错误的行。为此，我希望将单个用户的EntitlementJSON属性的内容选择到变量中。然后，我将检查"location"：{}是否存在。如果存在，则这是一个错误记录。例如，我希望看到employeeID 1为：

"Test Role 1": {
        "dodaac": {},
        "fundCode": {},
        "glRepair": {},
        "location": {},
        "cognos": {},
        "jv": {}
      },
      "Test Role 2": {
        "dodaac": {},
        "fundCode": {},
        "glRepair": {},
        "location": {},
        "cognos": {},
        "jv": {}
      }

这里有一个db<>fiddle的例子
代码示例

CREATE TABLE TEST_JSON
    (    PROCESS_ID              NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
       JSON_DATA                             CLOB       CONSTRAINT check_json CHECK (JSON_DATA IS JSON)
    )
LOB (JSON_DATA) STORE AS SECUREFILE (
  ENABLE      STORAGE IN ROW
  CHUNK       8192
  RETENTION
  NOCACHE
  LOGGING);

-- TABLE ALTERS
ALTER TABLE TEST_JSON 
  ADD CONSTRAINT TEST_JSON_PK 
  PRIMARY KEY ( PROCESS_ID ) USING INDEX
  ENABLE;

    set serveroutput on

declare
      c                      clob;
  BEGIN

    c:= to_clob('[
      {
        "displayName": "Whitbuckle, Dalongrirlum",
        "employeeID": "1",
        "EntitlementJSON": {
          "Test Role 1": {
            "dodaac": {},
            "fundCode": {},
            "glRepair": {},
            "location": {},
            "cognos": {},
            "jv": {}
          },
          "Test Role 2": {
            "dodaac": {},
            "fundCode": {},
            "glRepair": {},
            "location": {},
            "cognos": {},
            "jv": {}
          }
        },
        "manager": "Urgaehilde Rubyforged",
        "company": "Bloodguard Industrie"
      },
      {
        "displayName": "Koboldbelly, Sitgrolin",
        "employeeID": "4",
        "EntitlementJSON": {},
        "manager": "Kogrubera Orcborn",
        "company": "Bloodguard Industrie"
     },
     {
        "displayName": "Longblade, Skolout",
        "employeeID": "2",
        "EntitlementJSON": {
          "Test Role 1": {
            "location": [
              "Rockwall Villa - RV",
              "Thunderbluff - TB"
            ]
          }
        },
        "manager": "Therrilyn Mithrilpike",
        "company": "Bloodguard Industrie"
      },
      {
        "displayName": "Warmcoat, Alfomdum",
        "employeeID": "3",
        "EntitlementJSON": {
          "Test Role 3": {
            "location": [
              "ALL"
            ]
          }
        },
        "manager": "Therrilyn Mithrilpike",
        "company": "Bloodguard Industrie"
      }
    ]');

    INSERT INTO TEST_JSON (JSON_DATA)
    VALUES (c);
    commit;

  END;

下面是我们运行的查询：

select process_id,
     display_name,
     employeeID,
     manager,
     listagg(TR1) within group (order by process_id, display_name, employeeID, manager) Role_TR1,
     listagg(TR2) within group (order by process_id, display_name, employeeID, manager) Role_TR2,
     listagg(TR3) within group (order by process_id, display_name, employeeID, manager) Role_TR3,
     listagg(TR4) within group (order by process_id, display_name, employeeID, manager) Role_TR4
from (select j.process_id,
             jt.display_Name,
             jt.employeeID,                        
             jt.manager,                            
             TR1,
             TR2,
             TR3,
             TR4
        from test_json j
             cross apply JSON_TABLE(j.JSON_DATA, '$[*]'
               COLUMNS (display_Name                  VARCHAR2(200 CHAR)  PATH '$.displayName',
                        employeeID                    VARCHAR2(20 CHAR) PATH '$.employeeID',
                        manager                       VARCHAR2(200 CHAR) PATH '$.manager',       
                        nested path '$.EntitlementJSON."Test Role 1"' columns
                          (TR1 VARCHAR2(4000 CHAR) FORMAT JSON WITH WRAPPER PATH '$.location[*]'),      
                        nested path '$.EntitlementJSON."Test Role 2"' columns
                          (TR2 VARCHAR2(4000 CHAR) FORMAT JSON WITH WRAPPER PATH '$.location[*]'),      
                        nested path '$.EntitlementJSON."Test Role 3"' columns
                          (TR3 VARCHAR2(4000 CHAR) FORMAT JSON WITH WRAPPER PATH '$.location[*]'),      
                        nested path '$.EntitlementJSON."Test Role 4"' columns
                          (TR4 VARCHAR2(4000 CHAR) FORMAT JSON WITH WRAPPER PATH '$.location[*]')      
                      )) jt
        where process_id = 1)
  --and jt.employeeID = '2')
group by process_id, employeeID, display_name, manager;

即使我们取消注解"andjt.employeeID='2'"行，employeeID2的位置仍然为空