在 Oracle 中搜索 JSON 数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29898750/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Search a JSON array in Oracle
提问by a_horse_with_no_name
I'm trying to use the new JSON features introduced in Oracle 12.1.0.2
我正在尝试使用 Oracle 12.1.0.2 中引入的新 JSON 特性
However I can't seem to find a way to look for a specific value in an array inside my JSON document.
但是，我似乎找不到在 JSON 文档中的数组中查找特定值的方法。
Consider the following table and data:
考虑下表和数据：
create table orders
(
id integer not null primary key,
details clob not null check (details is json (strict))
);
insert into orders (id, details) values
(1, '{"products": [{ "product": 1, "quantity": 5}, {"product": 2, "quantity": 1}], "delivery_address": "My hometown"}');
insert into orders (id, details) values
(2, '{"products": [{ "product": 42, "quantity": 1}, {"product": 10, "quantity": 2}], "comment": "Your website is too slow"}');
insert into orders (id, details) values
(3, '{"products": [{ "product": 543, "quantity": 1}], "discount": "15"}');
insert into orders (id, details) values
(4, '{"products": [{ "product": 738, "quantity": 12}], "discount": "32"}');
Now I'm trying to write a SQL query that returns all orders, where product #2 was ordered.
现在我正在尝试编写一个 SQL 查询，该查询返回所有订单，其中订购了产品 #2。
I can't use json_existsbecause it doesn't allow array expressions (and I wouldn't know how to specify the value anyway).
我不能使用，json_exists因为它不允许数组表达式（而且我不知道如何指定值）。
json_valueonly returns a single value, so I can't "iterate" over the array values.
json_value只返回一个值，所以我不能“迭代”数组值。
I tried:
我试过：
select *
from orders o
where json_value(details, '$.products[*].product') = '2';
but that didn't return anything.
但这并没有返回任何东西。
I also tried json_table, but that also seems to only take the first element from the array:
我也试过json_table，但这似乎也只从数组中取出第一个元素：
select *
from orders o,
json_table(o.details, '$' columns (product_id integer path '$.products[*].product')) t
where t.product_id = 2;
But that didn't show anything. Apparently the "star expansion" in the "array_step" doesn't expand the values in the json_table
但这并没有显示出任何东西。显然，“ array_step”中的“星扩展”不会扩展json_table
So my question is:
所以我的问题是：
how can I (based on the above sample data) retrieve all orders where the product with the number 2 has been ordered?
我如何（基于上述示例数据）检索已订购编号为 2 的产品的所有订单？
I am essentially looking for the equivalent to this Postgres query:
我本质上是在寻找与此 Postgres 查询等效的内容：
select *
from orders
where details @> '{"products": [{"product": 2}] }';
回答by Peter Henell
I do not have any installation of oracle available right now but I believe that the first string in json_table should be the path to the array which we want to produce rows from. Then inside COLUMNS, the path should be relative to the array, not the root.
我现在没有任何可用的 oracle 安装，但我相信 json_table 中的第一个字符串应该是我们想要从中生成行的数组的路径。然后在COLUMNS 中，路径应该相对于数组，而不是根。
Try this:
尝试这个：
select *
from orders o,
json_table(o.details, '$.products[*]'
columns (
product_id integer path '$.product'
)
) t
where t.product_id = 2;
回答by mark d drake
In 12.2 you can do this with JSON_EXISTS
在 12.2 中，您可以使用 JSON_EXISTS 执行此操作
SQL> WITH ORDERS as
2 (
3 select 1 as ID, '{"products": [{ "product": 1, "quantity": 5}, {"product": 2, "quantity": 1}], "delivery_address": "My hometown"}' as DETAILS
4 from dual
5 union all
6 select 2 as ID, '{"products": [{ "product": 42, "quantity": 1}, {"product": 10, "quantity": 2}], "comment": "Your website is too slow"}' as DETAILS
7 from dual
8 union all
9 select 3 as ID, '{"products": [{ "product": 543, "quantity": 1}], "discount": "15"}' as DETAILS
10 from dual
11 union all
12 select 4 as ID, '{"products": [{ "product": 738, "quantity": 12}], "discount": "32"}' as DETAILS
13 from dual
14 )
15 select *
16 from ORDERS
17 where JSON_EXISTS(DETAILS,'$?(@.products.product == $PRODUCT)' passing 2 as "PRODUCT")
18 /
ID
----------
DETAILS
--------------------------------------------------------------------------------
1
{"products": [{ "product": 1, "quantity": 5}, {"product": 2, "quantity": 1}], "d
elivery_address": "My hometown"}
SQL>
