Возвращать результаты запроса sql как JSON в oracle 12c

Фон

Мне нужно собрать несколько тысяч строк из Oracle и преобразовать их в JSON для использования в SlickGrid. В настоящее время я собираю строки в PHP, преобразовывая его из ISO в UTF-8 с iconv и экспортируя в json с json_encode. Вся операция занимает около 1 секунды на стороне БД и 5 секунд для генерации JSON. Это способ долго.

Вопрос

Я читал, что Oracle 12c поддерживает JSON, но я не могу найти именно то, что мне нужно.

Есть ли способ вернуть результат стандартного запроса sql в формате json?

предположительно, я хотел бы задать запрос, подобный этому:

SELECT * from table AS JSON

и получите действительный json, подобный этому:

[{"col1": "value1", "col2": 2}, {"col1": "valueOfRow2", "col2": 3}]

Важно то, что мне нужно, чтобы последовательности unicode были экранированы для меня, так как я использую кодировку ISO-8859-2 на стороне клиента, а JSON должен быть либо в UTF-8, либо в тех случаях, когда эти escape-последовательности не выполняются.

Ответ 1

Oracle 12c версия 12.1.0.2 (последняя версия от 11.11.2014) добавляет поддержку JSON: https://docs.oracle.com/database/121/NEWFT/chapter12102.htm#BGBGADCC

Он был доступен с 17 октября. https://blogs.oracle.com/db/entry/oracle_database_12c_release_1

Если вы не можете запланировать/работать с этой версией, есть отличный пакет, написанный Льюисом Каннингемом и Джонасом Крогсбуэлем: PL/JSON * http://pljson.sourceforge.net/

Это отличный пакет (я использовал его в многочисленных установках базы данных).

Приведенные примеры хороши и охватывают большинство сценариев.

declare 
  ret json;
begin
  ret := json_dyn.executeObject('select * from tab');
  ret.print;
end;
/

Ответ 2

12cR2 (доступно в облаке Oracle) поддерживает это изначально.

SQL> select JSON_ARRAY(EMPLOYEE_ID, FIRST_NAME,LAST_NAME) from HR.EMPLOYEES;

JSON_ARRAY(EMPLOYEE_ID,FIRST_NAME,LAST_NAME)
--------------------------------------------------------------------------------
[100,"Steven","King"]
[101,"Neena","Kochhar"]

или

SQL> select JSON_OBJECT('ID' is EMPLOYEE_ID , 'FirstName' is FIRST_NAME,'LastName' is LAST_NAME) from HR.EMPLOYEES;

JSON_OBJECT('ID'ISEMPLOYEE_ID,'FIRSTNAME'ISFIRST_NAME,'LASTNAME'ISLAST_NAME)
----------------------------------------------------------------------------
{"ID":100,"FirstName":"Steven","LastName":"King"}
{"ID":101,"FirstName":"Neena","LastName":"Kochhar"}

Ответ 3

Вы можете использовать xmltype для преобразования результата SQL в XML и JSON. См. Следующую статью для решения, которое будет работать для Oracle с версии 9. Вы также можете скачать пакет itstar_xml_util:

http://stefan-armbruster.com/index.php/12-it/pl-sql/12-oracle-xml-and-json-goodies

Простой пример с таблицей emp:

declare
  l_sql_string varchar2(2000);
  l_xml        xmltype;
  l_json       xmltype;
begin
  l_sql_string := 'select a.empno, a.ename, a.job from emp a';

  -- Create the XML aus SQL
  l_xml := itstar_xml_util.sql2xml(l_sql_string);

  -- Display the XML
  dbms_output.put_line(l_xml.getclobval());

  l_json := itstar_xml_util.xml2json(l_xml);
  -- Display the JSON
  dbms_output.put_line(l_json.getclobval());  
end;

Результат выглядит следующим образом:

{"ROWSET": [
    {
      "EMPNO": 7839,
      "ENAME": "KING",
      "JOB": "PRESIDENT"
    },
    {
      "EMPNO": 7698,
      "ENAME": "BLAKE",
      "JOB": "MANAGER"
    },
[...]
    {
      "EMPNO": 7934,
      "ENAME": "MILLER",
      "JOB": "CLERK"
    }
  ]}

Ответ 4

Поддержка Oracle 12c для JSON - это возможность хранить объекты JSON, запрашивать их и выбирать из них.

У вас есть табличный формат, и вам нужно отображать данные только как JSON. Таким образом, вы можете просто конкатенировать строки в {'col1': 'rowN1', 'col2': 'rowN2'} и сделать остальное на стороне клиента. Или вы можете использовать LISTAGG для получения всего документа. Пример: http://technology.amis.nl/2011/06/14/creating-json-document-straight-from-sql-query-using-listagg-and-with-clause/

Просто используйте ограничение SQL VARCHAR2 4000 символов.

Вы также можете посмотреть http://database-geek.com/2009/03/25/json-in-and-out-of-oracle-json-data-type/ Но я не думаю, что тип объекта oracle улучшит вашу производительность.

Еще один пример - экспортировать XML, используя XMLType. Затем конвертируйте XML в JSON. XMLType позаботится о специальных символах, а API вполне стабилен (вам не нужно будет переписывать свою программу для Oracle 14).

Ответ 5

Чтобы добавить к ответу в oracle 12.2, вы можете создать json так, как хотите.

SELECT JSON_ARRAY(
JSON_OBJECT (
         KEY 'number' VALUE s.number,
         KEY 'name' VALUE s.sname,
         KEY 'location' VALUE s.loc
          )
       ) AS student_det
FROM   student s;

Ответ 6

Просто попробуйте это:

:) жизнь счастлива

with data as
  ( select 
    xmlelement(e,regexp_replace('{"name":"'||colname||'"}', '[[:cntrl:]]', ''),',') col1
    from tblname
  )
  select
        rtrim(replace(replace(replace(xmlagg(col1).getclobval(),'&'||'quot;','"'),'<E>',''),'</E>',''),',')
        as very_long_json
  from data;

Ответ 7

Я не вижу решения Python (если вам нужно сбрасывать JSON).

Я написал json-ora-extract для экстентов среднего размера (потому что набор данных должен соответствовать доступной памяти).

Он использует модули wx_Oracle и json Python для чтения данных из базы данных Oracle (любая версия) и сбрасывает их в файл *.json.

Также существует возможность создания сжатого файла *.gz.