11 October 2018

ODC Appreciation Day - Comparing JSON, they thought of everything...

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:
7B2274686973223A2274686174227D
compare 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.

A big thank you to the team that implemented JSON in the database and for thinking of everything! Thanks ODC!

Links