Вопрос:
Я хочу проанализировать строку JSON, которая находится в столбце CLOB из таблицы Tests_1, и вставить ее в другую таблицу (Test_2).
Как это сделать в PL/SQL без использования библиотеки JSON?
create table Tests_1 ( value CLOB ) create table Test_2 (a date,b date,c number,d number, e number) INSERT INTO Tests_1 (value) VALUES (‘{ «a»:»01/01/2015″, «b»:»31/12/2015″, «c»:»11111111111″, «d»:»1111111111″, «e»:»1234567890″ }’); Лучший ответ:
Oracle 12c поддерживает JSON
если у вас есть существующая таблица, просто выполните
ALTER TABLE table1 ADD CONSTRAINT constraint_name CHECK (your_column IS json); SELECT t.your_column.id FROM table1 t;
Обратите внимание, что по какой-то причине t необходим псевдоним
Или полный пример:
CREATE TABLE json_documents ( id RAW(16) NOT NULL, data CLOB, CONSTRAINT json_documents_pk PRIMARY KEY (id), CONSTRAINT json_documents_json_chk CHECK (data IS JSON) ); INSERT INTO json_documents (id, data) VALUES (SYS_GUID(), ‘{ «FirstName» : «John», «LastName» : «Doe», «Job» : «Clerk», «Address» : { «Street» : «99 My Street», «City» : «My City», «Country» : «UK», «Postcode» : «A12 34B» }, «ContactDetails» : { «Email» : «john.doe@example.com», «Phone» : «44 123 123456», «Twitter» : «@johndoe» }, «DateOfBirth» : «01-JAN-1980», «Active» : true }’); SELECT a.data.FirstName, a.data.LastName, a.data.Address.Postcode AS Postcode, a.data.ContactDetails.Email AS Email FROM json_documents a; FIRSTNAME LASTNAME POSTCODE EMAIL ————— ————— ———- ————————- Jayne Doe A12 34B jayne.doe@example.com John Doe A12 34B john.doe@example.com 2 rows selected.
Дополнительная информация
Ответ №1
Поскольку вы указали, что не хотите использовать какую-либо библиотеку JSON, если формат исправлен, вы можете принудить его к чему-то, что вы могли бы проанализировать как XML, начиная с снятия фигурных фигурных скобок, замены двоеточий на знаки равенства и удаления двойные кавычки из первой части каждой пары имя/значение:
select regexp_replace(regexp_replace(value, ‘(^{|}$)’), ‘^»(.*)»:(«.*»)($|,)’, ‘1=2’, 1, 0, ‘m’) from tests_1; REGEXP_REPLACE(REGEXP_REPLACE(VALUE,'(^{|}$)’),’^»(.*)»:(«.*»)($|,)’,’1=2′,1,0 ——————————————————————————— a=»01/01/2015″ b=»31/12/2015″ c=»11111111111″ d=»1111111111″ e=»1234567890″
который вы можете использовать в качестве атрибутов фиктивного XML node; конвертируйте это в XMLType, и вы можете использовать XMLTable для извлечения атрибутов:
select x.a, x.b, x.c, x.d, x.e from tests_1 t cross join xmltable(‘/tmp’ passing xmltype(‘<tmp ‘ ||regexp_replace(regexp_replace(value, ‘(^{|}$)’), ‘^»(.*)»:(«.*»)($|,)’, ‘1=2’, 1, 0, ‘m’) || ‘ />’) columns a varchar2(10) path ‘@a’, b varchar2(10) path ‘@b’, c number path ‘@c’, d number path ‘@d’, e number path ‘@e’ ) x; A B C D E ———- ———- ————- ————- ————- 01/01/2015 31/12/2015 11111111111 1111111111 1234567890
Затем вы можете преобразовать строки в даты во время вставки:
insert into test_2 (a, b, c, d, e) select to_date(x.a, ‘DD/MM/YYYY’), to_date(x.b, ‘DD/MM/YYYY’), x.c, x.d, x.e from tests_1 t cross join xmltable(‘/tmp’ passing xmltype(‘<tmp ‘ || regexp_replace(regexp_replace(value, ‘(^{|}$)’), ‘^»(.*)»:(«.*»)($|,)’, ‘1=2’, 1, 0, ‘m’) || ‘ />’) columns a varchar2(10) path ‘@a’, b varchar2(10) path ‘@b’, c number path ‘@c’, d number path ‘@d’, e number path ‘@e’ ) x; select * from test_2; A B C D E ———- ———- ————- ————- ————- 2015-01-01 2015-12-31 11111111111 1111111111 1234567890
Это будет связано с тем, что некоторые пары имени/значения не существуют, и вы получите нули, если это произойдет.
Если все пары всегда будут там, вы можете просто обозначить строку и вытащить соответствующие части:
select to_date(regexp_substr(value, ‘[^»]+’, 1, 4), ‘DD/MM/YYYY’) as a, to_date(regexp_substr(value, ‘[^»]+’, 1, 8), ‘DD/MM/YYYY’) as b, to_number(regexp_substr(value, ‘[^»]+’, 1, 12)) as c, to_number(regexp_substr(value, ‘[^»]+’, 1, 16)) as d, to_number(regexp_substr(value, ‘[^»]+’, 1, 20)) as e from tests_1; A B C D E ———- ———- ————- ————- ————- 2015-01-01 2015-12-31 11111111111 1111111111 1234567890 Ответ №2
В версии 11.0.4 (конечно, нет версии 11.0.4) у вас есть как минимум два варианта (кроме написания парсера самостоятельно):
В зависимости от используемой версии РСУБД, вот несколько вариантов:
Первый: для Oracle 11.1.0.7 и выше установите Apex 5 и используйте пакет apex_json:
— here I have 12.1.0.1 version with version 5 of apex installed column ora_version format a21; column apex_version format a21; select (select version from v$instance) as ora_version , (select version_no from apex_release) as apex_version from dual; —drop table test_2; /* our test table */ create table test_2( c_a date, c_b date, c_c number, c_d number, c_e number ); select * from test_2; declare l_json_doc clob; begin dbms_output.put_line(‘Parsing json…’); l_json_doc := ‘{«a»:»01/01/2015″,»b»:»31/12/2015″, «c»:»11111111111″,»d»:»1111111111″, «e»:»1234567890″}’; apex_json.parse(l_json_doc); insert into test_2(c_a, c_b, c_c, c_d, c_e) values(apex_json.get_date(p_path=>’a’, p_format=>’dd/mm/yyyy’), apex_json.get_date(p_path=>’b’, p_format=>’dd/mm/yyyy’), to_number(apex_json.get_varchar2(p_path=>’c’)), to_number(apex_json.get_varchar2(p_path=>’d’)), to_number(apex_json.get_varchar2(p_path=>’e’))); commit; dbms_output.put_line(‘Done!’); end; / column c_c format 99999999999; select to_char(c_a, ‘dd/mm/yyyy’) as c_a , to_char(c_b, ‘dd/mm/yyyy’) as c_b , c_c , c_d , c_e from test_2;
Результат:
ORA_VERSION APEX_VERSION ——————— ——————— 12.1.0.1.0 5.0.2.00.07 1 row selected. Table created. no rows selected. Parsing json… Done! PL/SQL procedure successfully completed. C_A C_B C_C C_D C_E ———- ———- ———— ———- ———- 01/01/2015 31/12/2015 11111111111 1111111111 1234567890 1 row selected.
Второй: используйте opensource PL/JSON. Никогда не использовал его раньше, поэтому я пользуюсь этой возможностью, чтобы попробовать. Это очень похоже на apex_json.
declare l_json json; —json object l_json_doc clob; begin dbms_output.put_line(‘Parsing json…’); — parsing is done upon object instantiation l_json_doc := ‘{«a»:»01/01/2015″,»b»:»31/12/2015″, «c»:»11111111111″,»d»:»1111111111″, «e»:»1234567890″}’; l_json := json(l_json_doc); insert into test_2(c_a, c_b, c_c, c_d, c_e) values(to_date(l_json.get(‘a’).get_string, ‘dd-mm-yyyy’), to_date(l_json.get(‘b’).get_string, ‘dd-mm-yyyy’), to_number(l_json.get(‘c’).get_string), to_number(l_json.get(‘d’).get_string), to_number(l_json.get(‘e’).get_string)); commit; dbms_output.put_line(‘Done!’); end; column c_c format 99999999999; select to_char(c_a, ‘dd/mm/yyyy’) as c_a , to_char(c_b, ‘dd/mm/yyyy’) as c_b , c_c , c_d , c_e from test_2;
Результат:
C_A C_B C_C C_D C_E ———- ———- ———— ———- ———- 01/01/2015 31/12/2015 11111111111 1111111111 1234567890 01/01/2015 31/12/2015 11111111111 1111111111 1234567890 2 rows selected.
Введение json_table() в выпуске 12.1.0.2 заставляет JSON немного разбираться (просто для демонстрации):
insert into test_2 select to_date(c_a, ‘dd-mm-yyyy’) , to_date(c_b, ‘dd-mm-yyyy’) , c_c , c_d , c_e from json_table(‘{«a»:»01/01/2015″, «b»:»31/12/2015″, «c»:»11111111111″, «d»:»1111111111″, «e»:»1234567890″}’ , ‘$’ columns ( c_a varchar2(21) path ‘$.a’, c_b varchar2(21) path ‘$.b’, c_c varchar2(21) path ‘$.c’, c_d varchar2(21) path ‘$.d’, c_e varchar2(21) path ‘$.e’ )) ;
результат:
select * from test_2; C_A C_B C_C C_D C_E ———— ———— ———- ———- ———- 1/1/2015 12/31/2015 1111111111 1111111111 1234567890 Ответ №3
Из Oracle 18c вы можете использовать оператор TREAT AS JSON:
Улучшения SQL для JSON
Вы можете указать, что данное выражение SQL возвращает данные JSON, используя TREAT (… AS JSON).
TREAT (… AS JSON) позволяет указать, что возвращаемое значение из данного выражения SQL должно рассматриваться как данные JSON. Такие выражения могут включать вызовы функций PL/SQL и столбцы, заданные предложением SQL WITH. Новые представления данных позволяют легко получить доступ к информации о путях и типах для полей JSON, которые записываются для справочных руководств с индексами. Возврат сгенерированных и запрошенных данных JSON в экземплярах LOB расширяет область использования реляционных данных.
Этот оператор предоставляет способ информировать базу данных о том, что содержимое VARCHAR2, BLOB, CLOB должно рассматриваться как содержащее JSON. Это позволяет использовать ряд полезных функций, в том числе возможность использовать “Упрощенный синтаксис” для объектов базы данных, у которых нет ограничения “IS JSON”.
И в вашем примере:
create table Test_1(val CLOB); create table Test_2(a date,b date,c number,d number, e number); INSERT INTO Test_1(val) VALUES(‘{ «a»:»01/01/2015″, «b»:»31/12/2015″, «c»:»11111111111″, «d»:»1111111111″, «e»:»1234567890″ }’); INSERT INTO Test_2(a,b,c,d,e) SELECT val_as_json.a, val_as_json.b, val_as_json.c, val_as_json.d, val_as_json.e FROM (SELECT TREAT(val as JSON) val_as_json FROM Test_1) sub; COMMIT;