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 JSON 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://tools.ietf.org/html/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.
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 objects 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.