Last active
April 12, 2022 19:50
-
-
Save loiclefevre/2b0c8a8ff7be183be42434641a3cbda8 to your computer and use it in GitHub Desktop.
SQL Table Macro (19.7+)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 | |
Remark for Pluto :) | |
According to NASA, this is no more considered as the 9th planet: https://solarsystem.nasa.gov/planets/dwarf-planets/pluto/overview/ |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment