Как вернуть результат SQL запроса в формате JSON?

#sql #json #oracle #oracle12c #oracle18c

Мне необходимо выбрать из БД несколько тысячь записей и конвертировать их в 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}]


PS одно важное замечание: так как в клиенте стоит ISO-8859-2 кодировка, JSON должен
быть или в UTF-8, или содержать эранированные последовательности юникода.
    

Ответы

Ответ 1

В релизе 12.2 появились новые SQL функции для генерации JSON из реляционных данных.
Для задачи как в вопросе лучше подойдут - JSON_OBJECT и JSON_ARRAYAGG.

create table tab as
    select level col1, 'value '||level col2 from dual connect by level <= 2
/ 

select max (rownum) rn, json_arrayagg (
    json_object (
        key 'col1' value col1,
        key 'col2' value col2
    ) format json returning clob 
) as json_doc
from tab;


Вернёт:

        RN JSON_DOC                                                             
          
---------- ---------------------------------------------------------
         2 [{"col1":1,"col2":"value 1"},{"col1":2,"col2":"value 2"}] 


Для более объёмных данных: 

select rn, length (json_doc) json_size, json_doc from (
    <здесь запрос приведённый выше>
    cross join (select dummy from dual connect by level <= 1e5) 
    );

        RN  JSON_SIZE JSON_DOC                                                  
                     
---------- ---------- ---------------------------------------------------------
    200000    5600001 [{"col1":1,"col2":"value 1"},{"col1":2,"col2":"value 2"},


5,6M документ за чуть более 1 сек. на слабой тестовой системе.



В версии 19c синтаксис функции JSON_OBJECT значительно упростился. Запрос из примера
выше будет выглядеть так:

select json_arrayagg (  
    json_object (*) returning clob   
) as json_doc  
from tab;


На Live SQL.


Ответ 2

Начиная с версии Oracle 18c можно воспользоваться PTF (polimorphic table function),
чтобы добавить колонку с JSON представлением результата выборки из любой таблицы.
 Например:

create table tab1 as
    select level col1, 'value '||level col2, systimestamp+level created  
    from dual connect by level <= 3
/

select * from serialize.addJSON (tab1) 
where col2 like 'value%' --какое-то условие выборки
;


Выведет:

 COL1 COL2       CREATED             JSON                                       
                                    
----- ---------- ------------------- -------------------------------------------------------------
    1 value 1    2019-08-04 12:27:52 {"COL1":1, "COL2":"value 1", "CREATED":"2019-08-04
12:27:52"}
    2 value 2    2019-08-05 12:27:52 {"COL1":2, "COL2":"value 2", "CREATED":"2019-08-05
12:27:52"}
    3 value 3    2019-08-06 12:27:52 {"COL1":3, "COL2":"value 3", "CREATED":"2019-08-06
12:27:52"}


Реализация самой функции не так сложна, как это может показаться на первый взгляд:

create or replace package serialize as
    function addJSON (tab table) return table pipelined row polymorphic using serialize;
    function describe (tab in out dbms_tf.table_t) return dbms_tf.describe_t;
    procedure fetch_rows;
end serialize;
/
create or replace package body serialize as
    function describe (tab in out dbms_tf.table_t) return dbms_tf.describe_t is
    begin
        for i in 1..tab.column.count loop 
            continue when not dbms_tf.supported_type (tab.column (i).description.type);
            tab.column(i).for_read := true;
        end loop;
        return dbms_tf.describe_t (
            new_columns => dbms_tf.columns_new_t (1 => dbms_tf.column_metadata_t(name
=> 'JSON')));   
    end;

    procedure fetch_rows as 
        rowset dbms_tf.row_set_t;
        newcol dbms_tf.tab_varchar2_t;
        rowcnt pls_integer;
    begin
        dbms_tf.get_row_set (rowset, rowcnt);
        for rn in 1..rowcnt loop
            newcol (rn) := dbms_tf.row_to_char (rowset, rn, format=>dbms_tf.format_json); 
        end loop;
        dbms_tf.put_col (1, newcol);
    end; 
end serialize;
/