Простой парсер JSON на PL/SQL

    Буквально вчера внезапно возникла задача — понадобилось разобрать данные в формате JSON непосредственно в хранимой процедуре Oracle. Разумеется, именно для таких вещей в Oracle и была добавлена Java, но хотелось чего-то более своего и написанного непосредственно на PL/SQL. Результаты своего порыва я и выкладываю на суд общественности. Вдруг кому пригодится.

    Для начала, полезно определиться с тем, что мы собираемся делать. Пусть исходные данные лежат в CLOB-поле какой-то таблицы:

    create table ae_spec (
      id             number                           not null,
      name           varchar2(30)                     not null,
      json           CLOB
    );
    
    alter table ae_spec add
      constraint pk_ae_spec primary key(id);
    

    Результат разбора будем складывать в дерево, размещенное во временной табличке и использовать непосредственно в той-же транзакции, в которой разбираем данные:

    create global temporary table ae_json (
      id                 number                           not null,
      parent_id          number,
      name               varchar2(1000),
      value              varchar2(1000)
    ) on commit delete rows;
    

    Загрузив данные в эту табличку, мы сможем использовать всю мощь SQL для их обработки.

    Теперь все готово для разработки нашего маленького пакета:

    Заготовка пакета
    create or replace package ae_spec_pkg as
        procedure compile(p_name in varchar2);
    end ae_spec_pkg;
    /
    
    create or replace package body ae_spec_pkg as
    
        procedure compile(p_name in varchar2) as
        begin
          -- Разбираем JSON
          load(p_name);
          -- TODO: Обрабатываем данные
        
          commit;
        exception
          when others then
            rollback;
            raise;
        end;
    
    end ae_spec_pkg;
    /
    


    Разбор JSON будет удобно разбить на две процедуры. Задачей сканера (процедура load) является просмотр исходного текста и выделение из него потока лексем:

    Сканер
    create or replace package body ae_spec_pkg as
    
        g_spec_state  constant number default 0;
        g_name_state  constant number default 1;
        
        procedure lexem(p_state in number
                       ,p_value in varchar2) as
        begin
          insert into ae_script_log(id, tp, value)
          values (ae_script_log_seq.nextval, p_state, p_value);
        end;                   
    
        procedure load(p_name in varchar2) as
        l_lob  CLOB;
        l_str  varchar2(1000) default null;
        l_len  number default null;
        l_pos  number default 1;
        l_ix   number default 1;
        l_st   number default g_spec_state;
        l_ch   varchar2(1) default null;
        l_val  varchar2(1000) default null;
        l_qt   varchar2(1) default null;
        l_bs   number default 0;
        begin
          select json into l_lob from ae_spec where name = p_name for update; 
          dbms_lob.open(l_lob, dbms_lob.lob_readonly);
          l_len := dbms_lob.getlength(l_lob);
          while l_pos <= l_len loop
            l_str := dbms_lob.substr(l_lob, 1000, l_pos);
            l_ix := 1;
            while l_ix <= length(l_str) loop
              l_ch := substr(l_str, l_ix, 1);
              if not l_qt is null then
                 if l_bs = 1 then
                    if not l_ch in (chr(13), chr(10)) then
                       l_val := l_val || l_ch;
                       l_bs := 0;
                    end if;
                    goto l;
                 end if;
                 if l_ch = '\' then
                    l_bs := 1;
                    goto l;
                 end if;
                 if l_ch = l_qt then
                    lexem(l_st, l_val);
                    l_st := g_spec_state;
                    l_qt := null;
                 else
                    l_val := l_val || l_ch;
                 end if;
                 goto l;
              end if;
              if l_ch in ('{', '}', '[', ']', ':', ',', ' ', chr(9), chr(13), chr(10)) then
                 if l_st = g_name_state then
                    lexem(l_st, l_val);
                 end if;
                 if l_ch in ('{', '}', '[', ']', ':', ',') then
                    lexem(g_spec_state, l_ch);
                 end if;
                 l_st := g_spec_state;
                 goto l;
              end if;
              if l_ch in ('''', '"') then
                 l_val:= null;
                 l_qt := l_ch;
                 l_st := g_name_state;
                 l_bs := 0;
                 goto l;
              end if;
              if l_st = g_name_state then
                 l_val := l_val || l_ch;
              else
                 l_val := l_ch;
                 l_st  := g_name_state;
              end if;
              <<l>>
              l_ix := l_ix + 1;
            end loop;
            l_pos := l_pos + 1000;
          end loop;
          if l_st = g_name_state then
             lexem(l_st, l_val);
          end if;
          dbms_lob.close(l_lob);
        exception
          when others then
            if dbms_lob.isopen(l_lob) = 1 then dbms_lob.close(l_lob); end if; 
            raise;
        end;
        ...
    end ae_spec_pkg;
    /
    


    Поскольку JSON — очень простой формат, нашему сканеру достаточно всего двух состояний (g_spec_state — ожидание очередного управляющего символа и g_name_state — ожидание продолжения ввода имени или значения).

    Для того, чтобы убедиться в правильности разбора, результат пока будем помещать в табличку — лог. Убедившись на нескольких тестовых примерах, что все работает как задумано, внесем изменения в lexem, для сохранения полученных данных в дерево (попутно вносим небольшие изменения в load, чтобы все работало):

    Готовый парсер
    create or replace package body ae_spec_pkg as
    
        g_spec_state  constant number default 0;
        g_name_state  constant number default 1;
        
        e_syntax_error        EXCEPTION;
        pragma EXCEPTION_INIT(e_syntax_error, -20001);
    
        procedure lexem(p_state in number
                       ,p_value in varchar2
                       ,p_node  in out NOCOPY number) as
        l_id number default null;
        l_vl ae_json.name%type;                   
        begin
          if p_state = g_spec_state then
             if p_value in ('}', ']', ',') then
                select parent_id into p_node from ae_json where id = p_node; 
             end if;
             if p_value in ('{', '[', ',') then
                select max(id) + 1 into l_id from ae_json;
                insert into ae_json(id, parent_id) values (l_id, p_node);
                p_node := l_id;
             end if;
             if p_value = ':' then
                select name into l_vl from ae_json where id = p_node;
                if l_vl is null then
                   RAISE_APPLICATION_ERROR(-20001, 'Syntax error');
                end if;
             end if;
          else
             select name into l_vl from ae_json where id = p_node;
             if l_vl is null then
                update ae_json set name = p_value where id = p_node;
             else
                select value into l_vl from ae_json where id = p_node;
                if not l_vl is null then
                   RAISE_APPLICATION_ERROR(-20001, 'Syntax error');
                end if;
                update ae_json set value = p_value where id = p_node;
             end if;
          end if;
        end;                   
    
        procedure load(p_name in varchar2) as
        l_lob  CLOB;
        l_str  varchar2(1000) default null;
        l_len  number default null;
        l_pos  number default 1;
        l_ix   number default 1;
        l_st   number default g_spec_state;
        l_ch   varchar2(1) default null;
        l_val  varchar2(1000) default null;
        l_qt   varchar2(1) default null;
        l_bs   number default 0;
        l_node number default 0;
        begin
          insert into ae_json(id) values (l_node);
          select json into l_lob from ae_spec where name = p_name for update; 
          dbms_lob.open(l_lob, dbms_lob.lob_readonly);
          l_len := dbms_lob.getlength(l_lob);
          while l_pos <= l_len loop
            l_str := dbms_lob.substr(l_lob, 1000, l_pos);
            l_ix := 1;
            while l_ix <= length(l_str) loop
              l_ch := substr(l_str, l_ix, 1);
              if not l_qt is null then
                 if l_bs = 1 then
                    if not l_ch in (chr(13), chr(10)) then
                       l_val := l_val || l_ch;
                       l_bs := 0;
                    end if;
                    goto l;
                 end if;
                 if l_ch = '\' then
                    l_bs := 1;
                    goto l;
                 end if;
                 if l_ch = l_qt then
                    lexem(l_st, l_val, l_node);
                    l_st := g_spec_state;
                    l_qt := null;
                 else
                    l_val := l_val || l_ch;
                 end if;
                 goto l;
              end if;
              if l_ch in ('{', '}', '[', ']', ':', ',', ' ', chr(9), chr(13), chr(10)) then
                 if l_st = g_name_state then
                    lexem(l_st, l_val, l_node);
                 end if;
                 if l_ch in ('{', '}', '[', ']', ':', ',') then
                    lexem(g_spec_state, l_ch, l_node);
                 end if;
                 l_st := g_spec_state;
                 goto l;
              end if;
              if l_ch in ('''', '"') then
                 l_val:= null;
                 l_qt := l_ch;
                 l_st := g_name_state;
                 l_bs := 0;
                 goto l;
              end if;
              if l_st = g_name_state then
                 l_val := l_val || l_ch;
              else
                 l_val := l_ch;
                 l_st  := g_name_state;
              end if;
              <<l>>
              l_ix := l_ix + 1;
            end loop;
            l_pos := l_pos + 1000;
          end loop;
          if l_st = g_name_state then
             lexem(l_st, l_val, l_node);
          end if;
          if l_node <> 0 then
             RAISE_APPLICATION_ERROR(-20001, 'Syntax error');
          end if;
          dbms_lob.close(l_lob);
        exception
          when others then
            if dbms_lob.isopen(l_lob) = 1 then dbms_lob.close(l_lob); end if; 
            raise;
        end;
        
        procedure compile(p_name in varchar2) as
        begin
          load(p_name);
          -- TODO:
        
          commit;
        exception
          when others then
            rollback;
            raise;
        end;
    
    end ae_spec_pkg;
    /
    


    В этой реализации я не старался отлавливать все возможные синтаксические ошибки в JSON-данных, ограничившись обнаружением ошибок приводящих к безусловной невозможности разбора данных. В этих случаях, все изменения выполненные в транзакции откатываются и возвращается соответствующее исключение.

    Кроме того, разработанный парсер (совершенно осознанно с моей стороны) допускает значительные послабления в части форматирования исходных данных. Например он может разобрать следующее описание, вообще говоря, не проходящее валидацию как JSON-данные:

    { tables:     { AD_ACTIVATION_TYPE:              { ID:               { attribute:  id }
                                                     , ACT_DATE:         { attribute:  start_date 
                                                                         , sql:       "is null"
                                                                         }
                                                     , ACT_PRIORITY:     { attribute:  priority }
                                                     , TYPE_ID:          { attribute:  subtype }
                                                     , ACT_STATE:        { attribute:  state 
                                                                         , sql:       "= 1"
                                                                         }
                                                     }
                  }
    , attributes: { id:                              { type:               integer
                                                     , is_mandatory
                                                     }
                  , start_date:                      { type:               date }
                  , priority:                        { type:               integer }
                  , subtype:                         { type:               integer
                                                     , is_mandatory 
                                                     }
                  , state:                           { type:               integer
                                                     , is_mandatory 
                                                     }
                  }
    }
    

    Конечно, при необходимости, разбор данных можно ужесточить, добавив необходимые проверки, но я не вижу в этом большого смысла, поскольку корректные JSON данные разбираются без каких либо проблем.

    На этом все. Буду рад если мой пост окажется кому-то полезным.
    AdBlock похитил этот баннер, но баннеры не зубы — отрастут

    Подробнее
    Реклама

    Комментарии 13

      0
      но зачем если оракл это и так умеет?
        +1
        Ну например затем, чтобы парсить не совсем JSON, как в примере в конце
          0
          А не сложно будет уточнить что вы имели в виду? Какой-то из стандартных пакетов PL/SQL умеет работать с чистым json? Можно название для того чтобы мог полистать документацию? Т.к. я что-то пока не нашёл ничего в стандартной поставке.
          Видел java-библиотеки прикрученные к PL/SQL… причём, похоже не «родные». Типа того же PL/JSON. Или стороннего DBMS_JSON.
          Я что-то пропустил или не там ищу?
            0
            Судя по первоначальному тексту комментария (пришедшему мне на почту), имелась в виду та ссылка, которую я давал в начале статьи.
              0
              Хм, но это Oracle Java, а не Oracle Database.
              Конечно, джава код можно встраивать в PL/SQL… но производительность двух методов ещё стоит сравнить. Вообще добавление поддержки джавы достаточно ресурсоёмкое…
                0
                Начиная с 8i в Oracle Database встроенная java. Я не пробовал прикручивать код по этой ссылке на сервере, не могу сказать что все сразу будет работать. Кроме того, есть сторонние пакеты для разбора JSON написанные на чистом PL/SQL. Тут нет никакой ракетной науки.
                  0
                  На счёт «встроенной» джавы — это несколько общая фраза. Взаимодействие Оракла (в частности мы говорим сейчас о PL/SQL Engine) и JVM может быть реализовано несколькимим методами и я этого не отрицал, обратите внимание. Лучше сравните производительность, это будет действительно интересно.

                  Ну и если мы вспомним версии «начиная с 8», то могу напомнить что особенно на старых версиях поддержка джавы не включалась в стандартную установку. При добавлении оной, словарь и набор системных объектов уверичивался на приблизительно 30 000 объёктов! Если не верите, можете проверить на вашей базе, сделайте что-то типа:

                  SELECT count(*)
                  FROM user_objects
                  WHERE object_name NOT LIKE 'SYS_%' AND
                  object_name NOT LIKE 'CREATE$%' AND
                  object_name NOT LIKE 'JAVA$%' AND
                  object_name NOT LIKE 'LOADLOB%' AND
                  object_type LIKE 'JAVA %'

                  И да, ракетной науки нету, так попробуйте. В большинстве случаев эквивалентный код будет работать не быстрее (а за счёт переключения контекста — ещё и медленнее), на то уже много статей написано, повторятся не буду.
                    0
                    Согласен практически по всем пунктам. Вопрос производительности, в этой задаче, правда не стоит. Ну и вряд ли можно сейчас найти современный Oracle без установленной Java. По поводу сторонних пакетов тоже согласен. Предпочитаю несложные вещи делать сам.
                    0
                    Про сторонние я и спрашивал выше. Стандартных пакетов нету. Сторонние — палка о двух концах. С одной стороны не тратим время на разработку, с другой — в случае проблем можем не получить support.
            0
            Интересно, а для чего используете JSON? По примеру похоже, для хранения метаданных. Из разобранного дерева стоите динамический DDL для создания таблиц?
              0
              Только для кастомизации, а не для создания. Я не хотел бы в этом топике углубляться в эту тему, поскольку оффтопик.
              Если интересно, можно пообщаться в приватной переписке.
              0
              и все же вопрос «НУ ЗАЧЕМ»???????? Я бы за такое руки поотбивал. Очередной вселенский хак, который выглядит как вроде как прикольное решение но на самом деле вы даже не представляете, что сидите на бомбе замедленного действия. И как раз проблема в том и есть, что то, что не проходит «валидацию json» у вас ее пройдет.

              P.S. Чем STRUCT и ARRAY вам службу не сослужили?
                +1
                Затем, что мне понадобился удобный формат для описания своих метаданных. Поясню, мне не понадобился ИМЕННО JSON. Именно JSON (с его ARRAY-ами и STRUCT-ами) как таковой мне не особо нужен. Но формат JSON показался мне удобным для МОИХ целей (в отличии от неудобного в этой задаче XML). Вот только закавычивать каждое имя я морально не готов. В результате я написал парсер данных СВОЕГО формата. Заодно он парсит JSON (правда не валидирует его и парсит кучу того, что сам JSON и Вы с ним предаете анафеме). Мои задачи этот парсер решает. Возможно решит чьи-то еще. А если он Вам лично не нравится, так я ведь пользоваться им не заставляю.

              Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

              Самое читаемое