如何使Oracle将JSON与JSON而不是字符串进行比较 [英] How to make Oracle compare JSON as JSON, not as Strings
问题描述
我正在努力查询包含来自外部系统的数据的JSON列.
I am struggling with querying a JSON column that contains data from an external system.
请考虑以下测试数据:
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"
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"}';
将仅返回主foo_id = 1的行,而不返回另一行.使用JSON_OBJECT()代替字符串文字不会改变任何内容.
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.
我也尝试过:json_query(payload, '$.ref') = json_object('id' value 1, 'type' value 'type1')
和json_query(payload, '$.ref') = json_query('{"id":1,"type":"type1"}', '$')
,但同样只找到了一行
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
根据JSON RFC( https://tools.ietf.org/html/rfc7159)键的顺序无关紧要.
According to the JSON RFC (https://tools.ietf.org/html/rfc7159 ) the order of keys is irrelevant.
因此对象{"id": 1, "type": "type1"}
和{"type": "type1", "id": 1}
是相同的,应被视为相等,并且上面的查询应返回两行(至少这是我对JSON rfc的理解)
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)
本质上,我正在寻找一种行为,其行为类似于以下Postgres查询(返回两行):
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
假定payload
被定义为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';
但是,这要求用于查询表的JSON对象必须解析并拆分成其元素.对于像这样的简单示例,这在某种程度上是可以接受的,但是如果JSON更复杂(或嵌套在多个级别),这将成为一场噩梦.
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.
在比较它们之前,有什么方法可以告诉Oracle对json_query(payload, '$.ref')
返回的JSON对象进行规范化"吗?
Is there any way I can tell Oracle to "normalize" the JSON object that is returned by json_query(payload, '$.ref')
before comparing them?
甚至更好:我可以告诉Oracle将它们作为真实的对象"(=键/值对)而不是纯字符串进行比较吗?
Or even better: can I tell Oracle to compare them as real "objects" (=key/value pairs) rather the plain strings?
理想的解决方案是,我可以在Java代码中简单地准备一条语句,并可以插入任意JSON作为参数.
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.
目前,我正在Oracle 12.2.0.1.0上对此进行测试,但是如果也有针对12.1的解决方案,那将是很好的选择.
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.
推荐答案
当您幸运地升级到18c时,这很容易:使用JSON_equal.
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...
您可以使用JSON_table将JSON转换为关系格式:
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
然后使用比较JSON进行相同的操作.并使用SQL设置差异进行比较.有点麻烦...
Then do the same with your comparison JSON. And use SQL set difference to compare them. Which is a bit of a faff...
或者在12.2上,您可以使用JSON_object以相同的顺序重建具有所有属性的对象:
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"}
这篇关于如何使Oracle将JSON与JSON而不是字符串进行比较的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!