Tuesday, 3 January 2017

Next Step in Row generation in Oracle Database 12c SQL using JSON_TABLE

One of those things SQL developers are frequently looking at is the generation of rows: having a query return records that do not really exist. For example to generate test data or to produce records for all days in a month. Tom Kyte usually selects from data dictionary views. Various tricks make the rounds, for example based on CONNECT BY or CUBE or UNPIVOT.

In this article, I am demonstrating a new – as far as I know – way of generating records. The underlying mechanism I am using is the JSON_TABLE operator that was introduced in Oracle Database 12cR1 (12.1.0.1) in 2014. This operator can expose data from a JSON document in a relational way – similar to what TABLE does to collections and XMLTABLE does for XML documents.

If the input to JSON_TABLE is a JSON Array, then the operator will produce a record for each element in the array.

For example:

Next Step in Row generation in Oracle Database 12c SQL using JSON_TABLE

A JSON Array is a simple string: starting with a square bracket and ending with one, in between it contains elements separated by commas: [1,2]  or [‘a’,’b’’,’c’]. In order to generate a specific number of rows we need a JSON array with that specific number of elements. Since the JSON document is simply a string – we can use string operators to create the array. And we have in RPAD (and LPAD) a function that can build up a string according to specifications: rpad(‘1’,21,’,2’) will return a string that consists of the character 1 followed by the string ‘,2’ as many times as fits in 21 characters (10 times): 1,2,2,2,2,2,2,2,2,2,2.

The query to produce a JSON array with the desired number of elements is as simple as:

select rpad('1',-1+2*(:desired_number_of_elements),',2') from dual

Knowing that we can easily produce a JSON document with an array holding the desired number of elements and knowing that JSON_TABLE will turn that document into relational records on the fly, we can create the query to generate rows:

SELECT r.*
FROM   JSON_TABLE('['||rpad('1',-1+2*( <strong>42</strong>),',1')||']', '$[*]'
COLUMNS (
"rn" PATH '$'
)) r;

assuming 42 is the desired number of rows. Plug in your own favorite number of rows.

Next Step in Row generation in Oracle Database 12c SQL using JSON_TABLE

Generating a list of weekdays can now be implemented as follows:

SELECT to_char( rownum+ to_date('19-09-2016','DD-MM-YYYY'),'DAY') day
FROM   ( select rws.rn
from   JSON_TABLE('['||rpad('1',-1+2*(7),',1')||']', '$[*]'
COLUMNS (
"rn" PATH '$'
)
) rws
)  days

Next Step in Row generation in Oracle Database 12c SQL using JSON_TABLE

The generation of the JSON document can be pushed to an inline PL/SQL Function like this:

with
function json_array_desired_length
(p_desired_length number)
return varchar2
is
begin
return '['||rpad('1',-1+2*p_desired_length,',1')||']';
end;
select to_char( rownum+ to_date('19-09-2016','DD-MM-YYYY'),'DAY') day
from (  select rws.rn
from JSON_TABLE( json_array_desired_length(7), '$[*]'
COLUMNS (
"rn" PATH '$'
)) rws
) days

And this function could be a utility function in a global package as well obviously, rendering the query as follows:

select to_char( rownum+ to_date('19-09-2016','DD-MM-YYYY'),'DAY') day
from (  select rws.rn
from JSON_TABLE( util.json_array_desired_length(7), '$[*]'
COLUMNS (
"rn" PATH '$'
)) rws
) days

or more compactly:

select to_char( rownum+ to_date('19-09-2016','DD-MM-YYYY'),'DAY') day
from (  select rws.rn from JSON_TABLE( util.json_array_desired_length(7), '$[*]' COLUMNS ("rn" PATH '$' )) rws ) days