how can I get the specified content by the string-CodePudding

QUESTION DESCRIPTION:

My oracle query SQL result have a column, the column value is a string description like as follow. How can I get the special substring that I want, such as I only want the pilot's name.

{"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"}

EXPECTED: How can I get the special substring such as follow I want substring:

|杜佳佳|,|凯尔|,|贺云|,|张三|

and I want a column become more columns according to this column

first pilot name	second pilot name	....
杜佳佳	凯尔	贺云...

CodePudding user response：

First you use JSON_QUERY to extract the values from the "pilotDTOList", then split the strings into rows, finally use PIVOT to convert rows to columns.
Tested on dbfiddle

WITH tmp AS (
SELECT REGEXP_SUBSTR(JSON_QUERY(column_value, '$.pilotDTOList'), '[^|] ',1,level) AS parts
FROM jsontable
CONNECT BY REGEXP_SUBSTR(JSON_QUERY(column_value, '$.pilotDTOList'), '[^|] ',1,level) IS NOT NULL
)

SELECT *
FROM (
    SELECT parts, ROWNUM AS rn 
    FROM tmp 
    WHERE parts NOT LIKE '%"%'
)
PIVOT (
    MAX(parts)
    FOR RN IN (
    1 AS first_pilot_name,
    2 AS second_pilot_name,
    3 AS third_pilot_name,
    4 AS fourth_pilot_name
    )
)
;

CodePudding user response：

You can extract each of the items in the JSON array directly into different columns (eliminating the need to PIVOT) and then find the substrings (without needing to use [slow] regular expressions):

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

Which, for the sample data, outputs:

FIRST_PILOT_NAME SECOND_PILOT_NAME THIRD_PILOT_NAME FOURTH_PILOT_NAME

杜佳佳凯尔贺云张磊3

FIRST_PILOT_NAME	SECOND_PILOT_NAME	THIRD_PILOT_NAME	FOURTH_PILOT_NAME
杜佳佳	凯尔	贺云	张磊3

db<>fiddlle here