Source Data : TABLE
JSON_STRING
| ID | JSON_STRING |
| 1 | [{"code":"MI007","descr":"James Bond"}] |
| 2 | [{"code":"MI007","descr":"James Bond"},{"code":"MI005","descr":"Max Well"}] |
| 3 | [{"code":"MI007","descr":"James Bond"},{"code":"MI005","descr":"Max Well"},{"code":"MI004","descr":"Donald Trump"}] |
ID 2 and 3 have multiple code and descr
In order to split/ extract the Json string in such a way that,
The code and descr should be splitted/extracted for same ID's which results in multiple rows.
FUNCTION "XYZ_JSON" ( )
RETURNS TABLE (ID NVARCHAR(50),
CODE_VALUE NVARCHAR(255),
TEXT_VALUE NVARCHAR(255)
)
LANGUAGE SQLSCRIPT
SQL SECURITY DEFINER AS
BEGIN
-- Create a temporary table to store the final results
DECLARE result_table TABLE (
ID NVARCHAR(50),
CODE_VALUE NVARCHAR(255),
TEXT_VALUE NVARCHAR(255)
);
-- Declare working variables
DECLARE current_row_id INT; -- Holds the row ID for the current row
DECLARE remaining_string NVARCHAR(5000); -- Holds the remaining string to process
DECLARE code_match NVARCHAR(255); -- Stores the currently matched value
DECLARE descr_match NVARCHAR(255);
DECLARE i INT; -- Match occurrence counter
DECLARE cursor input_sql for SELECT ID,JSON_STRING, OCCURRENCES_REGEXPR('code":"' IN "JSON_STRING" ) as occur_code
FROM "TABLE/CAL.VIEW";
-- Loop through each row of the input table
FOR cur_row AS input_sql
-- Replace this with your actual table name
DO
-- Initialize variables for processing the current row
current_row_id := :cur_row.id; -- Current row ID
remaining_string := :cur_row.json_string; -- Current row's string to process
IF cur_row.occur_code = 0 OR cur_row.occur_code IS NULL THEN
INSERT INTO :result_table VALUES (cur_row.id, cur_row.json_string, NULL);
END IF;
FOR i IN 1..cur_row.occur_code DO
code_match := SUBSTRING_REGEXPR('code":"([^"]*)"' IN remaining_string OCCURRENCE i GROUP 1);
descr_match := SUBSTRING_REGEXPR('descr":"([^"]*)"' IN remaining_string OCCURRENCE i GROUP 1);
-- Check if a match is found
IF code_match IS NOT NULL THEN
-- Add the match to the result table
INSERT INTO :result_table VALUES (current_row_id, code_match, descr_match);
ELSE
-- Exit the loop when no more matches are found
BREAK;
END IF;
END FOR;
END FOR;
-- Output the final results
return SELECT * FROM :result_table;
END;
Calling the table function.
select * from "XYZ_JSON"();
| ID | CODE_VALUE | TEXT_VALUE |
| 1 | MI007 | James Bond |
| 2 | MI007 | James Bond |
| 2 | MI005 | Max Well |
| 3 | MI007 | James Bond |
| 3 | MI005 | Max Well |
| 3 | MI004 | Donald Trump |
Bluesky
