Display JSON in APEX using APEX_MARKDOWN and JSON_TRANSFORM #JoelKallmanDay

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>

Links

• Markdown
• Path Expressions
• JSON_TRANSFORM
• JSON_MERGEPATCH