How to make Oracle compare JSON as JSON, not as Strings

When you're lucky enough to upgrade to 18c, this is easy: use JSON_equal.

This is a new condition which does exactly what you're asking:

select *from   foowhere  json_equal (  '{"type": "type1", "id":1}',  json_query(payload, '$.ref'));FOO_ID   PAYLOAD                                                                      1 {"data": {"k1": 1, "k2": "foo"}, "ref": {"id": 1, "type": "type1"}}          2 {"data": {"k1": 2, "k2": "bar"}, "ref": {"type": "type1", "id":1}} 

In the meantime, you'll have to go for something clunkier...

You could convert the JSON to a relational format using JSON_table:

select foo_id, id, typefrom   foo, json_table (  payload, '$' columns (    nested path '$.ref[*]' columns (      id path '$.id',      type path '$.type'    )  ));FOO_ID   ID   TYPE           1 1    type1          2 1    type1  

Then do the same with your comparison JSON. And use SQL set difference to compare them. Which is a bit of a faff...

Or on 12.2 you could use JSON_object to reconstruct the object with all the attributes in the same order:

with rws as (  select foo_id, id, type  from   foo, json_table (    payload, '$' columns (      nested path '$.ref[*]' columns (        id path '$.id',        type path '$.type'      )    )  )), j as (  select foo_id, json_object (           'id' value r.id, 'type' value r.type         ) j  from   rws r)  select * from j  where  j.j = '{"id":"1","type":"type1"}';FOO_ID   J                                  1 {"id":"1","type":"type1"}          2 {"id":"1","type":"type1"}  

Another thing you can do is to store the data as XML, not JSON. The reason why is that Oracle is "aware" of XML data and actually is able to interpret, manipulate and parse it correctly. It appears that Oracle's implementation of JSON isn't quite complete yet. It's simply stored as text and its syntax is validated through the check constraint, but the database kernel doesn't have the capability of actually understanding the data as it does with XML. This may change in a future version, but for now it's a drawback.

There are many helper functions out there nowadays when dealing with XML and JSON; for example APEX has a very useful write_json procedure in the APEX_JSON package which can take an XMLTYPE as a direct input to produce JSON from it.

There are also procedures to turn the JSON into XML, for example:

https://oracle-base.com/articles/misc/apex_json-package-generate-and-parse-json-documents-in-oracle#json-to-xml

If the match literal

 '{"id":1,"type":"type1"}'

is generated, this simple workaround will work.

select foo.*  from foo  where json_query(payload, '$.ref') in ('{"id":1,"type":"type1"}','{"type":"type1","id":1}');