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

Question

I am struggling with querying a JSON column that contains data from an external system.

Consider the following test data:

create table foo  
(  
  foo_id integer primary key,   
  payload clob,   
  constraint ensure_json CHECK (payload IS JSON)  
);  
  
insert into foo values (1, '{"data": {"k1": 1, "k2": "foo"}, "ref": {"id": 1, "type": "type1"}}');  
insert into foo values (2, '{"data": {"k1": 2, "k2": "bar"}, "ref": {"type": "type1", "id":1}}');  
     

I would like to check if the "ref" section contains the key/value pairs "id:1" and "type:type1"

The keys I want to compare are dynamic and the keys in the payload are dynamic as well (as I said an external source is providing that). So the following query:

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

will only return the row with the primary foo_id = 1, but not the other row. Using JSON_OBJECT() instead of the string literal doesn't change anything.

I also tried: json_query(payload, '$.ref') = json_object('id' value 1, 'type' value 'type1') and json_query(payload, '$.ref') = json_query('{"id":1,"type":"type1"}', '$') but again only one row is found

According to the JSON RFC (https://www.rfc-editor.org/rfc/rfc7159 ) the order of keys is irrelevant.

So the objects {"id": 1, "type": "type1"} and {"type": "type1", "id": 1} are the same and should be considered equal and the above query should return both rows (at least that's my understanding of the JSON rfc)

Essentially I am looking for a query that would behave like the following Postgres query (which returns both rows):

select *
from foo
where payload -> 'ref' = '{"id": 1, "type": "type1"}'::jsonb

^{assuming that payload is defined as jsonb}

I know I can workaround this, using something like this:

select *  
from foo  
where json_value(payload, '$.ref.type') = 'type1'  
   and json_value(payload, '$.ref.id') = '1';  

However that requires that the JSON object that is used to query the table has to be parsed and split into its elements. For a simple example like that this is somewhat acceptable but if the JSON is more complicated (or nested on multiple levels) this becomes a nightmare.

Is there any way I can tell Oracle to "normalize" the JSON object that is returned by json_query(payload, '$.ref') before comparing them?

Or even better: can I tell Oracle to compare them as real "objects" (=key/value pairs) rather the plain strings?

The ideal solution would be one where I can simply have a prepared statement in my Java code and could plug-in an arbitrary JSON as the parameter.

Currently I am testing this on Oracle 12.2.0.1.0 but it would be nice if there was a solution for 12.1 as well.

Answer 1

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   foo
where  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, type
from   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"}