Creating escaped JSON within JSON #JoelKallmanDay

Recently I had the requirement to create an escaped JSON within another JSON. This looked like a straightforward job but was more complicated than that.

The first attempt

Suppose I have a table MY_TABLE with a JSON message stored in the JSON_COLUMN column (of datatype CLOB). The requirement is to create a new JSON, with the properties eventName and eventType, and a third property parameter, which holds the original JSON message from the table as an escaped string. Actually, the SQL to create that JSON is pretty simple, just select the CLOB containing the JSON as a property value, and it will be automatically escaped:

select json_object('eventName' value 'MoviesRequest'
                  ,'eventType' value 'Character'
                  ,'parameter' value json_column returning clob) as result
from   my_table
where  id = 1;

which yields:

{"eventName":"MoviesRequest","eventType":"Character","parameter":"[{\"id\":\"9e3f7ce4-b9a7-4244-b709-dae5c1f1d4a8\",\"name\":\"Harry Potter\",\"alternate_names\":[\"The Boy Who Lived\",\"The Chosen One\",\"Undesirable No. 1\",\"Potty\"],\"species\":\"human\",\"gender\":\"male\",\"house\":\"Gryffindor\",\"dateOfBirth\":\"31-07-1980\",\"yearOfBirth\":1980,\"wizard\":true,\"ancestry\":\"half-blood\",\"eyeColour\":\"green\",\"hairColour\":\"black\",\"wand\":{\"wood\":\"holly\",\"core\":\"phoenix tail feather\",\"length\":11},\"patronus\":\"stag\",\"hogwartsStudent\":true,\"hogwartsStaff\":false,\"actor\":\"Daniel Radcliffe\",\"alternate_actors\":[],\"alive\":true,\"image\":\"https://ik.imagekit.io/hpapi/harry.jpg\"}]"}

Exactly what I want to achieve!

Now the row with id = 1 contains a JSON without any formatting. But when the JSON is formatted, for example using the following command:

insert into my_table(id, json_column)
select 3
     , json_serialize(json_column returning clob pretty) 
from   my_table 
where  id = 1;

and I execute the same SQL on the row with id = 3, I get

{"eventName":"MoviesRequest","eventType":"Character","parameter":"[\n  {\n    \"id\" : \"9e3f7ce4-b9a7-4244-b709-dae5c1f1d4a8\",\n    \"name\" : \"Harry Potter\",\n    \"alternate_names\" :\n    [\n      \"The Boy Who Lived\",\n      \"The Chosen One\",\n      \"Undesirable No. 1\",\n      \"Potty\"\n    ],\n    \"species\" : \"human\",\n    \"gender\" : \"male\",\n    \"house\" : \"Gryffindor\",\n    \"dateOfBirth\" : \"31-07-1980\",\n    \"yearOfBirth\" : 1980,\n    \"wizard\" : true,\n    \"ancestry\" : \"half-blood\",\n    \"eyeColour\" : \"green\",\n    \"hairColour\" : \"black\",\n    \"wand\" :\n    {\n      \"wood\" : \"holly\",\n      \"core\" : \"phoenix tail feather\",\n      \"length\" : 11\n    },\n    \"patronus\" : \"stag\",\n    \"hogwartsStudent\" : true,\n    \"hogwartsStaff\" : false,\n    \"actor\" : \"Daniel Radcliffe\",\n    \"alternate_actors\" :\n    [\n    ],\n    \"alive\" : true,\n    \"image\" : \"https://ik.imagekit.io/hpapi/harry.jpg\"\n  }\n]"}

Now the escaped JSON contains space, and escaped newline characters, which I do not want to have in my final message.

Getting rid of the whitespace

The solution would be to convert the CLOB to a real JSON, where the pretty print will disappear, and then convert to CLOB again. This can be done through the function JSON_SERIALIZE ( TREAT ( json_column AS JSON ) RETURNING CLOB ) which would return a CLOB containing the JSON without any whitespace.

Unfortunately, when I apply this function in my query:

select json_object('eventName' value 'MoviesRequest'
                  ,'eventType' value 'Character'
                  ,'parameter' value ( json_serialize ( treat ( json_column as json ) returning clob ) ) returning clob) as result
from   my_table
where  id = 3;

the result will be

{"eventName":"MoviesRequest","eventType":"Character","parameter":[{"id":"9e3f7ce4-b9a7-4244-b709-dae5c1f1d4a8","name":"Harry Potter","alternate_names":["The Boy Who Lived","The Chosen One","Undesirable No. 1","Potty"],"species":"human","gender":"male","house":"Gryffindor","dateOfBirth":"31-07-1980","yearOfBirth":1980,"wizard":true,"ancestry":"half-blood","eyeColour":"green","hairColour":"black","wand":{"wood":"holly","core":"phoenix tail feather","length":11},"patronus":"stag","hogwartsStudent":true,"hogwartsStaff":false,"actor":"Daniel Radcliffe","alternate_actors":[],"alive":true,"image":"https://ik.imagekit.io/hpapi/harry.jpg"}]}

Now the parameter property is not an escaped string, but a JSON array. The function JSON_SERIALIZE ( TREAT ( json_column AS JSON ) RETURNING CLOB ) in this case obviously does not return a CLOB, but a genuine JSON.

I have tried all sorts of things to get it to escape the JSON, until I found (and I cannot remember where) that applying a function to the JSON_SERIALIZE should convert it to a CLOB. So, I needed a function that would not change the CLOB itself, and SUBSTR(x,1) appeared to me as a logical candidate. Applying this, I got the query

select json_object('eventName' value 'MoviesRequest'
                  ,'eventType' value 'Character'
                  ,'parameter' value substr ( ( json_serialize ( treat ( json_column as json ) returning clob ) ) ,1 ) returning clob ) as result
from   my_table
where  id = 3;

and indeed, that works! I now got the JSON as an escaped string, without the whitespace characters:

{"eventName":"MoviesRequest","eventType":"Character","parameter":"[{\"id\":\"9e3f7ce4-b9a7-4244-b709-dae5c1f1d4a8\",\"name\":\"Harry Potter\",\"alternate_names\":[\"The Boy Who Lived\",\"The Chosen One\",\"Undesirable No. 1\",\"Potty\"],\"species\":\"human\",\"gender\":\"male\",\"house\":\"Gryffindor\",\"dateOfBirth\":\"31-07-1980\",\"yearOfBirth\":1980,\"wizard\":true,\"ancestry\":\"half-blood\",\"eyeColour\":\"green\",\"hairColour\":\"black\",\"wand\":{\"wood\":\"holly\",\"core\":\"phoenix tail feather\",\"length\":11},\"patronus\":\"stag\",\"hogwartsStudent\":true,\"hogwartsStaff\":false,\"actor\":\"Daniel Radcliffe\",\"alternate_actors\":[],\"alive\":true,\"image\":\"https://ik.imagekit.io/hpapi/harry.jpg\"}]"}