JSON Sibling Arrays – Without Ordinal Association

Oracle’s JSON_TABLE provides a facility to un-nest arrays using the nested path clause, but if you try to use that with sibling arrays you can end up with unexpected results. Sibling arrays here mean separate arrays, but at the same level under the same parent object.

There are two types of sibling array scenarios:
1. Without Ordinal Association – Array items have no connection through ordinal position, they are separate aspects under the parent object.
2. With Ordinal Association – Array items are linked across the sibling arrays by ordinal position. So the n^th array object in one array is linked in some way to the n^th array object in the sibling array. This is covered in my post JSON Sibling Arrays – With Ordinal Association.

In this post, I’ll cover the first situation, without ordinal association. Let’s create an example to explain what I mean, in this case data for Tennis Doubles’ teams.

Converting Roman Numerals to Decimal

Oracle’s TO_CHAR function can convert a decimal into Roman Numerals, for example :

select to_char(1334, 'FMRN') RN from dual;

RN
-------------
MCCCXXXIV

The format model ‘FMRN‘ is made up of two parts,
FM = Fill Mode, this modifier suppresses whitespace padding in the return value.
RN = Roman numerals (upper case). You can use rn for lower case Roman Numerals.

However, Oracle doesn’t have a function to do the reverse, converting Roman Numerals to decimal, so here’s a solution I’ve written for that:
Note, the function raises a VALUE_ERROR exception if the input isn’t a valid Roman Numeral.

create or replace function roman_to_number(pRomanNumeral varchar2) return number deterministic is
  vLastValue number := 0;
  vCurrValue number;
  vRomanNumeral varchar2(100) := upper(pRomanNumeral);
  vResult number;
begin
  if vRomanNumeral is not null then
    vResult := 0;
    for i in reverse 1..length(vRomanNumeral)
    loop
      vCurrValue := case substr(vRomanNumeral, i, 1)
                      when 'I' then 1
                      when 'V' then 5
                      when 'X' then 10
                      when 'L' then 50
                      when 'C' then 100
                      when 'D' then 500
                      when 'M' then 1000
                    end;
      vResult := vResult + case when vCurrValue < vLastValue then -vCurrValue else vCurrValue end;
      vLastValue := vCurrValue;
    end loop;
    if vResult is null or vRomanNumeral <> to_char(vResult, 'FMRN') then
      raise VALUE_ERROR;
    end if;
  end if;  
  return vResult;
end;
/