In the previous post Austin discovered this piece of code.
DECLARE
v VARCHAR2(30);
BEGIN
-- loop through the sparse IBT values and
-- load them into the JSON Array
v := v_unique_sites.FIRST;
LOOP
EXIT WHEN v IS NULL;
v_array2.APPEND(v);
v := v_unique_sites.NEXT(v);
END LOOP;
END;
Produced this perplexing result.
["\"Hospital\""]
It turns out it’s valid JSON. Text values without an associated key they get “escaped” with backslashes. The variable is VARCHAR2 and populated with strings eg: Hospital – thus when appended to the JSON Array they get “escaped”.
This is avoided by converting the text to JSON as shown in this code.
DECLARE
v VARCHAR2(30);
BEGIN
-- loop through the sparse IBT values and
-- load them into the JSON Array
v := v_unique_sites.FIRST;
LOOP
EXIT WHEN v IS NULL;
v_JSON := JSON(v); --> make it JSON here!!!
v_array2.APPEND(v_json);
v := v_unique_sites.NEXT(v);
END LOOP;
END;
Now the output is what Austin wants.
["Hospital"]
The next post will delve further into converting from and to JSON.
Thanks for reading.