The last posting showed how JSON object and arrays can be generated from existing (relational ) data. The examples showed scalar column data being assembled into a more or less complex JSON objects and arrays.
But what if a piece of data is already JSON?
For instance if a function returns JSON data in a VARCHAR2 data type?
CREATE OR REPLACE function getPersonData return VARCHAR2 IS
BEGIN
return ‘{“name”: “Beda”}’;
END;
/
When a JSON generation function receives such value it does not know that the contents is JSON – and consequently put the value in double quotes:
SELECT JSON_OBJECT(‘person’ VALUE getPersonData)
FROM DUAL;
{“person”:”{\”name\”: \”Beda\”}”}
Although the generated JSON object is syntactically correct JSON it is most likely not the desired output. The double quotes turn the inner JSON object into a simple string value likely leading to wrong results if that data is queried of further processed.
How can we avoid the double quotes?
By telling the generation function that one input is already JSON: this is done with the FORMAT JSON clause:
SELECT JSON_OBJECT(‘person’ VALUE getPersonData FORMAT JSON)
FROM DUAL;
{“person”:{“name”: “Beda”}}
If the database can deduce that a SQL value contains JSON data the FORMAT JSON clause can be omitted.
For instance if the input to a JSON generation function is the output of a function that always returns JSON data (e.g. JSON_QUERY, JSON_OBJECT, JSON _ARRAY).
Similarly, a column with an IS JSON check constraint is known to only contain JSON data; thus the column value won’t get quoted.
CREATE TABLE t (data VARCHAR2(200));
INSERT INTO t VALUES(‘{“name” : “Beda”}’);
SELECT JSON_OBJECT(‘person’ VALUE data) FROM t;
{“person”:”{\”name\” : \”Beda\”}”}
ALTER TABLE t ADD CONSTRAINT ensureJson CHECK (data IS JSON);
SELECT JSON_OBJECT(‘person’ VALUE data) FROM t;
{“person”:{“name” : “Beda”}}