# 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:

```sql
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:

```json
{"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:

```sql
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

```json
{"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:

```sql
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

```json
{"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

```sql
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:

```json
{"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\"}]"}
```