正在将JSON_ARRAY_T转换为SQL表达式的表达式列表

Question

在PL/SQL中，我可以从负载中读取并获取JSON_ARRAY_T对象。假设我想查询由ids标识的文档列表。有效载荷中发送的JSON是：

{"id": [1, 2, 3]}

我的PL/SQL就像

DECLARE 
payload JSON_OBJECT_T;
idArray JSON_ARRAY_T;
cur SYS_REFCURSOR;
BEGIN
payload := JSON_OBJECT_T.parse(:body_text);

idArray := payload.get_Array('id');
OPEN cur FOR
SELECT * FROM INVOICES WHERE id IN idArray;
:result := cur;
END;

然而，我在说ORA-00932: inconsistent datatypes: expected NUMBER got SYS.JSON_ARRAY_T时遇到了一个错误。我该怎么做？

Answer 1

这里的问题是"在"；子句的右侧需要一个列表，但无法处理jsonarray_t实例。

一种选择是将数组取消填充为数字。为了做到这一点；在"；子句将是jsontable的结果，jsontable取消填充输入数组。

drop table invoices;
create table invoices (id number);
insert into invoices values (2);
insert into invoices values (4);
select * from invoices where id in 
(select * from json_table('{"id":[1,2,3]}', '$.id[*]' columns (a path '$')));

另一种替代方案是将json_array转换为varray/嵌套表；在"；条款与上面相同，但需要varray/嵌套表作为中间步骤。

drop table invoices;
create table invoices (id number);
insert into invoices values (2);
insert into invoices values (4);
drop type narr;
create type narr as array(5) of number;
/
select * from invoices where id in
(select * from table
(select json_value('{"id":[1,2,3]}', '$.id' returning narr)));

也就是说，如果你想继续使用json_array_t，那么你可能想迭代json_array_t的元素，如下所示：

DECLARE 
payload JSON_OBJECT_T;
idArray JSON_ARRAY_T;
idx number;
BEGIN
payload := JSON_OBJECT_T.parse('{"id":[1,2,3]}');

idArray := payload.get_Array('id');
for idx in 1..idArray.get_Size loop
dbms_output.put_line(idArray.get(idx-1).to_Number());
end loop;
END;
/

Answer 2

使用JSON_TABLE:在SQL中完成所有操作

BEGIN
OPEN :result FOR
SELECT i.*
FROM   INVOICES i
INNER JOIN JSON_TABLE(
:body_text,
'$.id[*]'
ERROR ON ERROR
COLUMNS (
id NUMBER PATH '$'
)
) j
ON (i.id = j.id);
END;
/

db<gt；小提琴这里