sql – 在Oracle中搜索JSON数组

发布时间：2020-12-12 07:49:17 所属栏目：MsSql教程来源：网络整理

导读：我试图使用Oracle 12.1.0.2中引入的新的 JSON功能但是我似乎找不到一种在JSON文档中的数组中寻找特定值的方法. 考虑下表和数据： create table orders( id integer not null primary key,details clob not null check (details is json (strict)));insert in

我试图使用Oracle 12.1.0.2中引入的新的 JSON功能

但是我似乎找不到一种在JSON文档中的数组中寻找特定值的方法.

考虑下表和数据：

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,"discount": "15"}');

insert into orders (id,details) values 
(4,'{"products": [{ "product": 738,"quantity": 12}],"discount": "32"}');

现在我正在尝试编写一个SQL查询,返回所有订单,其中订购了产品#2.

我不能使用json_exists,因为它不允许数组表达式(我不知道如何指定值).

json_value只返回一个值,所以我不能“遍历”数组值.

我试过了：

select *
from orders o
where json_value(details,'$.products[*].product') = '2';

但是没有返回任何东西.

我也试过json_table,但也似乎只取数组中的第一个元素：

select *
from orders o,json_table(o.details,'$' columns (product_id integer path '$.products[*].product')) t
where t.product_id = 2;

但是没有显示任何东西.显然,“array_step”中的“星展”不会扩展json_table中的值

所以我的问题是：

我如何(根据上述样本数据)检索所有已订购了2号产品的订单？

我本质上正在寻找相当于这个Postgres的查询：

select *
from orders
where details @> '{"products": [{"product": 2}] }';

解决方法

我现在没有安装oracle可用,但我相信json_table中的第一个字符串应该是要从中生成行的数组的路径.
然后在COLUMNS内,路径应该是相对于数组,而不是根.

尝试这个：

select *
from orders o,'$.products[*]' 
         columns (
              product_id integer path '$.product'
         )
     ) t
where t.product_id = 2;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!