问题描述:
我的 oracle 查询 SQL 结果有一列,该列 value 是一个字符串描述,如下所示。如何获得我想要的特殊子字符串,比如我只想要飞行员的名字。
{"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"}
预期:如何获取特殊子字符串,例如我想要的子字符串:
|杜佳佳|,|凯尔|,|贺云|,|张三|
我希望一列根据此列成为更多列
first pilot name | second pilot name | .... |
---|---|---|
杜佳佳 | 凯尔 | 贺云... |
回答1
您可以将 JSON 数组中的每个项目直接提取到不同的列中(无需 PIVOT
),然后找到子字符串(无需使用 [slow] 正则表达式):
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
db<>fiddlle https://dbfiddle.uk/?rdbms=oracle_21&fiddle=4e6c3f96a174b6bc30cfaa627ded2155