При преобразовании JSON в объектный тип данных json_value возвращает неинициализированный экземпляр объекта
В 19c появилсь возможность напрямую преобразовать JSON как результат SQL запроса в объектный тип данных (Mapping of JSON Data To and From SQL Object Types).
Посмотрел пример из документации и решил попробовать:
create or replace type partRow is object (name varchar2 (16), qty number);
/
create or replace type partRows is table of partRow;
/
create or replace type itemRow is object (name varchar2 (16), parts partRows);
/
with data as (
select
'{"name":"item1",
"parts": [{"name": "part1", "qty":"1"}, {"name": "part2", "qty": "2"}]}' item
from dual)
select
json_value (item, '$.name') itemname,
json_value (item, '$.parts[0].name') partname,
json_value (item, '$' returning itemRow) item
from data;
Выводит:
ITEMNAME PARTNAME ITEM(NAME, PARTS)
-------- -------- -----------------------
item1 part1 ITEMROW(NULL, NULL)
Получаю неинициализированный экземпляр itemRow. Скалярные значения itemname,partname из JSON, как и в прошлых версиях, получаю как и ожидалось.
Как это можно исправить?
Ответы (1 шт):
В главе 16.3 Using JSON_VALUE To Instantiate a User-Defined Object Type Instance указано:
The field names of the targeted JSON object are compared with the SQL names of the object attributes. [...] If the names do not match (case insensitively, by default), then a mismatch error occurs.
Имена полей в JSON должны совпадать с именами атрибутов обьектного типа данных (по умолчанию, без учёта регистра). Но на самом деле, сравнение производится с учётом регистра.
Добавив ON MISMATCH клазулу, запрос как в вопросе завершится ошибкой:
with data as (
select
'{"name":"item1",
"parts": [{"name": "part1", "qty":"1"}, {"name": "part2", "qty": "2"}]}' item
from dual)
select
json_value (item, '$' returning itemRow error on mismatch) item
from data;
ORA-40601: missing data for object type conversion
После заключения всех имён атрибутов в двойные кавычки, чтобы исключить несовпадение имён:
create or replace type partRow is object ("name" varchar2 (16), "qty" number);
/
create or replace type partRows is table of partRow;
/
create or replace type itemRow is object ("name" varchar2 (16), "parts" partRows);
/
Тот же запрос вернётся без ошибки и с ожидаемым результатм:
ITEM(name, parts(name, qty))
--------------------------------------------------------------------------------
ITEMROW('item1', PARTROWS(PARTROW('part1', 1), PARTROW('part2', 2)))
По видимому имеет место, или новый баг, или ошибка в документации.