Austin Tatious JSON’s His Characters

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.