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 г.