Reusable SQL templates: learn about SQL Macros🤯

CREATE TABLE planets (
    json_document BLOB,
    CONSTRAINT json_document_is_json CHECK (json_document IS JSON)
);

INSERT INTO planets (json_document) 
VALUES ( '[ {"name":"Mercury"}, {"name":"Venus"}, {"name":"Earth"}, 
            {"name":"Mars"}, {"name":"Jupiter"}, {"name":"Saturn"}, 
            {"name":"Uranus"}, {"name":"Neptune"} ]' );
COMMIT;

-- One row retrieved containing a JSON array
SELECT JSON_SERIALIZE(json_document) AS array FROM planets;

                                                       ARRAY
-------------------------------------------------------------
[ {"name":"Mercury"}, {"name":"Venus"}, {"name":"Earth"}, … ]


-- Second version where the column table is also a parameter
CREATE OR REPLACE FUNCTION unwind(t DBMS_TF.TABLE_T, c DBMS_TF.COLUMNS_T) 
       RETURN VARCHAR2 SQL_MACRO IS
BEGIN
  RETURN := q'{
      SELECT d.array_item AS JSON_DOCUMENT, d.order_id
        FROM unwind.t nested }'|| unwind.c(1) ||q'{ COLUMNS (
                 NESTED PATH '$[*]' COLUMNS (
                     array_item CLOB FORMAT JSON PATH '$',
                     order_id FOR ORDINALITY
                 )
             ) d}';
END;
/
-- Using a table and one of its columns as parameters of SQL Macro
SELECT * FROM unwind( planets, COLUMNS( json_document ) );         

     JSON_DOCUMENT   ORDER_ID
------------------ ----------
{"name":"Mercury"}          1
  {"name":"Venus"}          2
  {"name":"Earth"}          3
   {"name":"Mars"}          4
{"name":"Jupiter"}          5
 {"name":"Saturn"}          6
 {"name":"Uranus"}          7
{"name":"Neptune"}          8