Markdown is a plain text format for writing structured documents, and in APEX there is a built-in package that can transform Markdown notation into HTML.
With Markdown it is also possible to show pieces of code or JSON documents in a fixed size font. Having a fixed font to display JSON documents in APEX is easier to read than a non-fixed font.
The idea is to add Markdown to the JSON document and use the APEX_MARKDOWN package to convert the whole string in HTML.
Something like:
```json
{"this":"that"}
The first line in the Markdown starts with three ticks (`) followed by "json", then the JSON document start on a new line.
To setup the example, use the following script to create a table and add some sample data. The sample data consist of three different JSON documents. Note that the first document includes a PDF file, the Base64 value representind the PDF has been shortened for brevity. In reality the Base54 value is enormous, but there is a solution for that in this post.
create table t
(outgoing_message json) -- Oracle Database 23ai introduced the JSON datatype
/
insert into t (outgoing_message)
values (
'{
"orderNumber" : "APX000328573",
"invoiceNumber" : 2023300028,
"salesChannel" : "Amazon",
"file" :
{
"mime" : "application/pdf",
"data" : "255044462D312E340A25C3A4C3BCC3B6C39F0A322030206F626A0A3C3C2F4C656E6774682033203020522F46696C7465722F466C6"
}
}'
);
insert into t (outgoing_message)
values (
'{
"status" : "success",
"message" : "The order update was successfully received."
}
'
);
insert into t (outgoing_message)
values (
'{
"orderNumber" : "APX000328573",
"timeStamp" : "2023-04-13T14:49:50.453269Z",
"salesChannel" : "Amazon",
"financialStatus" : "paid",
"logisticStatus" : "ready for production",
"payments" :
[
{
"provider" : "Mollie",
"date" : "2023-04-13T16:49:50",
"amount" : 569.95,
"currency" : "EUR"
}
]
}');
commit;
Displaying the complete Base64 string representing the PDF document does not add any value for the user looking at the JSON document and is most likey irrelevant.
Using the JSON_TRANSFORM function it is trivial to replace the comple Base64 string into something that is good enough for the user, in this example we will replace it with "<Base64Encoded PDF Document>".
Depending on the version of the database that you're using, the syntax has changed slightly from Oracle Database 19c to Oracle Database 23ai, use the following snippet:
-- Oracle Database 19c
select json_transform (ann.outgoing_message
,set '$.file.data' = '<Base64Encoded PDF Document>' ignore on missing
returning clob
) as outgoing_message
from t ann
-- Oracle Database 23ai
select json_transform (ann.outgoing_message
,nested path '$' (
set '@.file.data' = '<Base64Encoded PDF Document>' ignore on missing
)
) as outgoing_message
from t ann
;
The next step is to wrap the resulting JSON document with the Markdown tags and a new line break:
apex_markdown.to_html ('```json'||chr(10)||
This results in the following query and result:
select json_transform (ann.outgoing_message
,nested path '$' (
set '@.file.data' = '<Base64Encoded PDF Document>' ignore on missing
)
) as outgoing_message
from t ann;
<pre><code class="language-json">{
"orderNumber" : "APX000328573",
"invoiceNumber" : 2023300028,
"salesChannel" : "Amazon",
"file" :
{
"mime" : "application/pdf",
"data" : "<Base64Encoded PDF Document>"
}
}
</code></pre>
