12.1.0.2 добавил поддержку JSON в базу данных Oracle. Это помогло вам работать с документами JSON, хранящимися в clobs или varchar2.

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

Однако это оставляет вам проблему. Получение данных обратно в формате JSON!

Попытаться написать собственный генератор JSON сложно. Поэтому в версии 12.2 мы предлагаем целый ряд возможностей, которые помогут вам выполнить работу.

JSON из SQL

12.2 предоставляет четыре ключевые функции, которые помогут вам написать SQL, возвращающий данные в формате JSON:

  • JSON_объект
  • JSON_objectagg
  • JSON_массив
  • JSON_arrayagg

Вы используете функции JSON_object* для создания серии документов с парами ключ-значение. то есть вывод имеет фигурные скобки {}. Функции JSON_array* принимают список значений и возвращают его в виде массива, то есть в квадратных скобках [].

Для каждой строки на входе версии этих функций без агг выводят строку. Версии agg объединяют несколько строк в один документ или массив.

В ПОРЯДКЕ. Итак, как они работают?

Давайте посмотрим на пример.

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

  • Название отдела
  • Массив его сотрудников
  • Каждый элемент этого массива должен быть отдельным документом, содержащим имя сотрудника и его должность.

Например:

{ 
  "department": "Accounting", 
  "employees": [ { 
    "name": "Shelley,Higgins", 
    "job": "Accounting Manager" 
  }, { 
    "name": "William,Gietz", 
    "job": "Public Accountant" 
  } ] 
}

Как вы создаете это, используя новые функции?

Работаем изнутри наружу:

  • Для начала нужен документ на каждого работника. У этого есть два атрибута, имя и работа. Передайте их в вызов JSON_object.
  • Затем вам нужно превратить их в массив. Так что оберните JSON_object в JSON_arrayagg. Сгруппируйте по отделам, чтобы выделить сотрудников для каждого из них в отдельный массив.
  • Наконец, у вас есть документ для каждого отдела. Итак, вам нужен еще один объект JSON_object с атрибутами отдела и сотрудников. Значениями для них являются название отдела и результаты вызова JSON_arrayagg на предыдущем шаге.

Соедините все вместе, и вы получите:

select json_object (
  'department' value d.department_name, 
  'employees' value json_arrayagg ( 
    json_object ( 
      'name' value first_name || ',' || last_name, 
      'job' value job_title 
    )
  )
) 
from  hr.departments d, hr.employees e, hr.jobs j 
where d.department_id = e.department_id 
and   e.job_id = j.job_id 
group by d.department_name;

И вуаля! У вас есть JSON!

JSON в PL/SQL

Итак, теперь у вас есть документ JSON. Но что, если вы хотите отредактировать его?

Скажем, вы хотите изменить имена на верхний регистр. И добавьте элемент заголовка. Таким образом, предыдущий документ становится:

{ 
  "department": "Accounting", 
  "employees": [ { 
    "name": "SHELLEY,HIGGINS", 
    "job": "Accounting Manager", "title": "" 
  }, {
    "name": "WILLIAM,GIETZ", 
    "job": "Public Accountant", 
    "title": "" 
  } ] 
}

Если вы создаете документ, проще всего добавить их в SQL! Итак, предполагается, что вы хотите изменить документ JSON из внешнего источника.

Чтобы помочь в этом, есть новые объекты PL/SQL. Они позволяют вам получать доступ, изменять и добавлять элементы в документ JSON с помощью вызовов get/put.

Основные типы объектов:

  • json_element_t — супертип для документов и массивов
  • json_object_t — для работы с документами JSON
  • json_array_t — для работы с массивами JSON

Первое, что вам нужно сделать, это создать объект JSON. Сделайте это, проанализировав документ:

doc := json_object_t.parse(' {
  "department": "Accounting", 
  "employees": [ { 
    "name": "Shelley,Higgins", 
    "job": "Accounting Manager" 
  }, { 
    "name": "William,Gietz", 
    "job": "Public Accountant" } ] 
  } '
);

Затем вы можете получить доступ к массиву сотрудников, используя get:

emps := treat(doc.get('employees') as json_array_t) ;

Функция обработки приводит элемент к соответствующему типу (здесь JSON_array_t).

Когда у вас есть массив, вы можете перебирать сотрудников. Put добавляет новый ключ, если его нет. В противном случае оно перезаписывает существующее значение.

for i in 0 .. emps.get_size - 1 loop 
  emp := treat(emps.get(i) as json_object_t); 
  emp.put('title', ''); 
  emp.put('name', upper(emp.get_String('name'))); 
end loop;

Функции get возвращают ссылку на исходный объект. Поэтому, если вы получите JSON и измените его, исходный документ также изменится!

Если вы этого не хотите, клонируйте элемент, когда получите его. Например:

emps := treat(doc.get('employees') as json_array_t).clone

Итак, полный блок PL/SQL для преобразования JSON:

declare 
  doc json_object_t; 
  emps json_array_t; 
  emp json_object_t; 
begin 
  doc := json_object_t.parse('{ 
      "department": "Accounting", 
      "employees": [ { 
        "name": "Shelley,Higgins", 
        "job": "Accounting Manager" 
      }, { 
        "name": "William,Gietz", 
        "job": "Public Accountant" 
      } ] 
    }'
  ); 
  emps := treat(doc.get('employees') as json_array_t) ; 
  for i in 0 .. emps.get_size - 1 loop 
    emp := treat(emps.get(i) as json_object_t); 
    emp.put('title', ''); 
    emp.put('name', upper(emp.get_String('name'))); 
  end loop;   
  dbms_output.put_line(doc.to_String); 
end; 
/
{ 
  "department": "Accounting", 
  "employees": [ { 
    "name": "SHELLEY,HIGGINS", 
    "job": "Accounting Manager", 
    "title": "" 
  }, { 
    "name": "WILLIAM,GIETZ", 
    "job": "Public Accountant", 
    "title": "" 
  } ] 
}

Теперь вы можете генерировать JSON из SQL и изменять его в PL/SQL, у вас есть мощные возможности для работы с ним.

И есть множество других улучшений функциональности JSON в 12.2. Другие улучшения включают в себя:

  • Функция JSON_exists
  • Поддержка In-Memory, секционирования и материализованных представлений
  • Поисковые индексы
  • GeoJSON
  • Руководство по данным JSON

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

Статья полностью опубликована на сайте blogs.oracle.com 10 ноября 2016 г.