Converting JSON_ARRAY_T to expression list for SQL expression

user_12947970 0
Viewed 259

In PL/SQL I can read and get a JSON_ARRAY_T object from the payload. Say I want to query a list of documents identified by their ids. The JSON sent in the payload is:

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

and my PL/SQL would be like

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;

I encounter an error however saying ORA-00932: inconsistent datatypes: expected NUMBER got SYS.JSON_ARRAY_T. How should I get this working?

oracle-ords plsql oracle sql
2 Answers

The problem here is that the "in" clause is expecting a list on its right side but can't process a json_array_t instance.

One option is to unflatten the array into numbers. In order to do this, the right side argument to the "in" clause would be the result of json_table, which unflattens the input array.

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

Another alternative is to convert the json_array into a varray/nested table and unflatten that on the right side of the "in" clause. Same as above, but needs the varray/nested table as an intermediate step.

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

That said, if you want to keep using json_array_t, then you might want to iterate over the elements of json_array_t like this:

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;
/
user_178574790

Do it all in SQL using JSON_TABLE:

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<>fiddle here

user_15092640
Related