我正在努力查询包含来自外部系统的数据的JSON列。

考虑以下测试数据：

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}}');

我想检查“ ref”部分是否包含键/值对“ id：1”和“ type：type1”

我要比较的键是动态的，有效负载中的键也是动态的（正如我所说的那样，它是由外部源提供的）。所以下面的查询：

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

将仅返回主foo_id = 1的行，而不返回另一行。使用JSON_OBJECT（）代替字符串文字不会改变任何内容。

我也尝试：json_query(payload, '$.ref') = json_object('id' value 1, 'type' value 'type1')与json_query(payload, '$.ref') = json_query('{"id":1,"type":"type1"}', '$')但同样只有一行被发现

根据JSON
RFC（https://tools.ietf.org/html/rfc7159），密钥的顺序无关紧要。

因此，对象{"id": 1, "type": "type1"}和{"type": "type1", "id": 1}是相同的，应被视为相等，并且上面的查询应返回两行（至少这是我对JSON rfc的理解）

本质上，我正在寻找一种查询，其行为类似于以下Postgres查询（返回两行）：

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

假设payload被定义为jsonb

我知道我可以使用以下方法解决此问题：

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

但是，这要求必须对用于查询表的JSON对象进行解析并将其拆分为元素。对于像这样的简单示例，这在某种程度上是可以接受的，但是如果JSON更复杂（或嵌套在多个级别），则将成为一场噩梦。

有什么方法可以告诉Oraclejson_query(payload, '$.ref')在比较它们之前“标准化”返回的JSON对象？

甚至更好：我可以告诉Oracle将它们作为真实的“对象”（=键/值对）而不是纯字符串进行比较吗？

理想的解决方案是，我可以在Java代码中简单地准备一条语句，并可以插入任意JSON作为参数。

目前，我正在Oracle 12.2.0.1.0上对此进行测试，但是如果也有针对12.1的解决方案，那将是很好的选择。