Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table_name (
  id INT GENERATED ALWAYS AS IDENTITY,
  value JSON
);
INSERT INTO table_name (value) VALUES (
  '{
    "flightRiskValue":{
      "airportDifficulty":0.00,
      "airportExp":0.00,
      "airportExp_captain":0.00,
      "airportTechNos":0.20,
      "airportTechNos_captain":0.40,
      "flightCrewNum":0.00,
      "flightExp":0.00,
      "flightExp_captain":0.00,
      "groupExp":0.70,
      "ilsWorkStatus":0.00,
      "pilotCrewLimit":0.00,
      "pilotCrewLimit_captain":0.00,
      "pilotDutyLevel":0.05,
      "pilotDutyLevel_captain":0.05,
      "pilotFindAnyFemale":0.00,
      "pilotScheduleTechLevel":0.00,
      "pilotScheduleTechLevel_captain":0.00,
      "pilotWorkTimeMonth":0.00,
      "pilotWorkTimeMonth_captain":0.00,
      "pilotWorkTimeWeek":0.15,
      "pilotWorkTimeWeek_captain":0.10,
      "pilotsIsDeptTeam":0.00,
      "rain":0.00,
      "rainAndSnow":0.00,
      "seeing":0.00,
      "snow":0.00,
      "wind":0.00
    },
    "pilotDTOList":[
      "副驾驶：5-2|杜佳佳|F3；",
      "副驾驶：5-1|凯尔|F4；",
      "第二机长/巡航机长：5-1|贺云|A1；",
      "机长：5-2|张磊3|A1；"
    ],
    "riskValue":1.95,
    "version":"1"
  }'
);
1 rows affected

SELECT SUBSTR(
         pilot1,
         INSTR(pilot1, '|', 1, 1) + 1,
         INSTR(pilot1, '|', 1, 2) - INSTR(pilot1, '|', 1, 1) - 1
       ) AS first_pilot_name,
       SUBSTR(
         pilot2,
         INSTR(pilot2, '|', 1, 1) + 1,
         INSTR(pilot2, '|', 1, 2) - INSTR(pilot2, '|', 1, 1) - 1
       ) AS second_pilot_name,
       SUBSTR(
         pilot3,
         INSTR(pilot3, '|', 1, 1) + 1,
         INSTR(pilot3, '|', 1, 2) - INSTR(pilot3, '|', 1, 1) - 1
       ) AS third_pilot_name,
       SUBSTR(
         pilot4,
         INSTR(pilot4, '|', 1, 1) + 1,
         INSTR(pilot4, '|', 1, 2) - INSTR(pilot4, '|', 1, 1) - 1
       ) AS fourth_pilot_name
FROM   table_name t
       CROSS APPLY JSON_TABLE(
         t.value,
         '$'
         COLUMNS (
           pilot1 NVARCHAR2(200) PATH '$.pilotDTOList[0]',
           pilot2 NVARCHAR2(200) PATH '$.pilotDTOList[1]',
           pilot3 NVARCHAR2(200) PATH '$.pilotDTOList[2]',
           pilot4 NVARCHAR2(200) PATH '$.pilotDTOList[3]'
         )
       ) j


FIRST_PILOT_NAME
SECOND_PILOT_NAME
THIRD_PILOT_NAME
FOURTH_PILOT_NAME


杜佳佳
凯尔
贺云
张磊3