26 November 2019

Human readable JSON, stored in BLOB

Currently Oracle (we're using Oracle 18c at the moment) is still recommending to store your JSON document in a BLOB column. More efficient, no character conversions and probably some more advantages.

When there is a need to look at the JSON document, having it as a BLOB is not very useful.

An easy way to make the JSON Blob readable is to use the following query:

select json_query (i.json_payload, '$' returning clob pretty)
  from imports i
Omitting "Pretty" will output the JSON object without formatting. Including Pretty makes it look... well... pretty.