Oracle 12c introduced JSON in the database and one of the most recent additions is a function to compare JSON, JSON_EQUAL.
From the documentation:
Oracle recommends that you use BLOB storage.When I first learned about this recommendation it surprised me. Looking at a BLOB doesn't even remotely look like JSON, so how does JSON_EQUAL deal with a comparison between a BLOB and a string?
I mean how does this:
7B2274686973223A2274686174227Dcompare to this:
{"testing":"information"}
Let's try and find out.
create table t
(json_data blob)
/
insert into t values (
utl_raw.cast_to_raw (
'{"testing":"information"}'
)
);
select case
when json_equal (json_data, '{"testing":"information"}')
then 'same' else 'different'
end as result
from t
/
RESULT
---------
same
select case
when json_equal (json_data, '{"testing":"different"}')
then 'same' else 'different'
end as result
from t
/
RESULT
----------
different
How cool is that?
Later I learned from Chris Saxon about the reason behind the BLOB recommendation: it avoids character set conversions!
So, there are no excuses for not using BLOB as your default choice when storing JSON documents.
