Как я могу разобрать строку JSON в PL/SQL

Вопрос:Я хочу проанализировать строку 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",

Вопрос:

Я хочу проанализировать строку 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;

Оцените статью
Добавить комментарий