# Занятие 11: Расширяемость / Слабоструктурированные данные > Исходная страница в нативном Markdown: Расширяемость / Слабоструктурированные данные --- Индекс LLMS: [llms.txt](/ru/llms.txt) --- > [!NOTE] > Эта страница извлечена из официального PDF в нативный Markdown. Текст слайдов, код и комментарии сохранены в порядке чтения; исходные PDF и HTML остаются эталоном макета. > **HTML source:** [dev2_11_ext_semistruct.html](https://edu.postgrespro.ru/16/dev2-16/dev2_11_ext_semistruct.html) > **Archive:** [DEV2-handouts-16.zip](https://edu.postgrespro.ru/16/DEV2-handouts-16.zip) > **Страниц PDF:** 29 ## Page 1: Расширяемость / Слабоструктурированные данные {#source-page-001} ### Авторские права © Postgres Professional, 2017–2024 Авторы: Егор Рогов, Павел Лузанов, Илья Баштанов, Игорь Гнатюк Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал) ### Использование материалов курса Некоммерческое использование материалов курса (презентации, демонстрации) разрешается без ограничений. Коммерческое использование возможно только с письменного разрешения компании Postgres Professional. Запрещается внесение изменений в материалы курса. ### Обратная связь Отзывы, замечания и предложения направляйте по адресу: edu@postgrespro.ru ### Отказ от ответственности Компания Postgres Professional не несет никакой ответственности за любые повреждения и убытки, включая потерю дохода, нанесенные прямым или непрямым, специальным или случайным использованием материалов курса. Компания Postgres Professional не предоставляет каких-либо гарантий на материалы курса. Материалы курса предоставляются на основе принципа «как есть» и компания Postgres Professional не обязана предоставлять сопровождение, поддержку, обновления, расширения и изменения. ## Page 2: Темы {#source-page-002} - Что такое слабоструктурированные данные - Применение в реляционных базах данных - Необходимые операции - Тип данных для XML: xml - Типы данных для JSON: json и jsonb - Индексирование документов JSON ## Page 3: Что это такое {#source-page-003} **Наиболее известные примеры: XML и JSON** - Схема данных определяется самим документом - документ содержит разметку для выделения элементов - и определения иерархии - Данные не соответствуют реляционной модели - конкретный документ можно представить в виде таблиц - документы общего вида тоже можно, но практически — неудобно ### Комментарии преподавателя Слабоструктурированные (semi-structured) данные — это данные, представленные не в реляционном (табличном) виде. Обычно это текстовый документ, но в отличие от текста на естественном языке, документ содержит разметку, определяющую структурные единицы. Широко известными примерами форматов (языков разметки) являются XML и JSON, в последнее время также популярен YAML. Структура, или схема, документа определяется его собственной разметкой. (Это не исключает возможности определить структуру отдельно — например, для XML есть языки DTD, XMLSchema.) Разметка, как правило, позволяет определить вложенные элементы, образующие иерархию. Конечно, любую конкретную иерархию можно представить и в табличном виде. Можно уложить в таблицы и произвольную иерархию без жесткой схемы, но результат будет лишен типизации данных, возможности создания ограничений целостности, с ним будет крайне неудобно работать в терминах запросов SQL. Поэтому с некоторыми видами данных действительно удобнее и логичнее работать именно в таком, слабоструктурированном, виде. ## Page 4: Применение {#source-page-004} **Интерфейсный формат** - между приложением и внешней системой - между клиентской и серверной частями приложения - Внутри базы данных как атомарное значение - только хранение и извлечение - не нарушает 1NF, достаточно обычных средств SQL - Внутри базы данных как неатомарное значение - операции с отдельными частями документа - SQL не достаточно; требуется специальный язык запросов - и набор дополнительных операций - гибкость, когда данные плохо укладываются в реляционную модель ### Комментарии преподавателя Документы XML или JSON могут применяться как удобный интерфейсный формат между разными компонентами, независимый от конкретной платформы или языка программирования. Например, для взаимодействия с внешними системами, или для обмена данными между клиентской и серверной частями системы. В последнем случае интерфейсный формат позволяет клиенту послать сложный запрос, а серверу выполнить его эффективным образом (в отличие от традиционного подхода, принятого в ORM, когда сервер базы данных нагружается большим количеством мелких запросов, не оставляя возможностей для оптимизации). Если слабоструктурированные данные хранятся внутри самой базы данных, то все зависит от того, как эти данные используются. Если внутри SQL запросов не делается попыток работать с отдельными частями документов, то такие значения можно считать атомарными с точки зрения СУБД. Тут достаточно обычных средств SQL. Конечно, более интересен случай, когда серверу необходимо выделять часть документа, фильтровать вывод на основе содержания документов и т. п. Для этого SQL уже не хватает и нужен специализированный язык доступа к слабоструктурированным данным. Если СУБД предоставляет такую возможность, это позволяет комбинировать строгий реляционный подход для данных, имеющих четкую структуру, и подход в духе NoSQL для данных, которые сложно представить в табличном виде. ## Page 5: Операции {#source-page-005} **Преобразование документа к реляционному виду** - сохранение транспортного документа в базу данных - использование средств SQL для обработки - Преобразование реляционных данных к виду документа - выгрузка данных в транспортный формат - Выделение части документа - специализированный язык запросов - Индексирование документов - поддержка операций специализированного языка запросов ### Комментарии преподавателя Чтобы работать со слабоструктурированными документами в реляционной базе данных, понадобятся возможности для преобразования данных из одного вида в другой, поскольку в одних ситуациях удобнее использовать реляционные средства, в других — документоориентированные. Особенно это важно, если такой язык, как XML или JSON, используется в качестве транспортного формата. Кроме этого, нужны средства для выделения части документа — каких-то отдельных значений, или какого-то подмножества элементов. Такие средства обычно представлены в виде специализированного языка запросов, который учитывает иерархическую структуру документов. Такие языки запросов можно использовать не только для выделения части документа, но и, например, для выбора тех документов, в которых присутствует заданный фрагмент. Если слабоструктурированные документы используются в реляционной СУБД, встает задача индексирования таких данных. Для этого, как правило, не годятся обычные B-деревья, поскольку требуется поддерживать не операции сравнения документов, а операции, связанные со специализированным языком запросов. ## Page 6: Формат XML {#source-page-006} **eXtensible Markup Language** - универсальный язык разметки, первый стандарт 1998 года - форматы на основе XML: XHTML, FB2, RSS и Atom, SVG, SOAP... - Структура - вложенные элементы - отделяются тегами, могут иметь атрибуты, содержат текст - Инструментарий - описание схемы (DTD, XML Schema), языки запросов XPath и XQuery, - язык преобразования XSLT - PostgreSQL: XPath 1.0 ### Комментарии преподавателя XML (eXtensible Markup Language) появился в контексте веба и разрабатывается консорциумом W3C. XML является текстовым документом, содержащим специальную разметку. XML определяет синтаксическую структуру разметки, которая с помощью тегов выделяет элементы (возможно, вложенные друг в друга), которые могут также иметь атрибуты. (Язык XML довольно сложен, в нем имеются также инструкции обработки, комментарии и другие сущности, но мы не будем на этом останавливаться.) XML позволяет определить расширения XML, определяя конкретный набор элементов и их атрибутов, и наделяя их определенной семантикой. К известным расширениям относятся XHTML (более строгий HTML, совместимый с XML), FB2 (формат электронных книг), RSS и Atom (форматы для новостных лент), SVG (векторная графика), SOAP (формат сообщений веб-сервисов) и другие. За время существования формата XML сформировался богатый инструментарий для работы с ним, включающий языки описания схемы документов (такие, как DTD, XMLSchema), языки запросов (базовый XPath и более богатый XQuery на его основе), языки преобразования документов XML (XSLT и XSL-FO на его основе). В реляционных базах работу с XML регламентирует стандарт SQL/XML. PostgreSQL поддерживает только XPath 1.0. Преобразования XSLT доступны в расширениях (xml2 или в сторонних). Индексирование не реализовано (хотя все возможности для этого есть). ## Page 7: Тип данных для XML {#source-page-007} **xml** - хранение в виде обычного текста + синтаксическая проверка - сохраняется исходное форматирование - может хранить XML-документ или фрагмент документа ### Комментарии преподавателя Тип xml хранит документ в виде текстовой строки. Но по сравнению с типом text он проверяет введенные значения на допустимость по правилам XML. Допускается хранение корректно оформленных документов, а также их фрагментов, в которых может быть несколько элементов верхнего уровня или текстовых узлов. Для типа xml не определены никакие операторы сравнения, поэтому столбец этого типа нельзя проиндексировать с помощью B-tree. Однако можно, например, использовать индекс по выражению, в котором вызывается XPath и результат приводится к строке символов. Существуют и другие ограничения реализации этого типа в PostgreSQL. ## Page 8: XML: тип xml {#source-page-008} ```sql => CREATE DATABASE ext_semistruct; ``` ```sql CREATE DATABASE ``` ```sql => \c ext_semistruct ``` ```text You are now connected to database "ext_semistruct" as user "student". ``` Будем работать с документом, описывающим компоненты компьютера. Теги выделяются угловыми скобками. Для каждого открывающего тега () есть соответствующий ему закрывающий, имя которого начинается на косую черту (). Такая пара тегов определяет элемент (computer). Атрибуты могут быть перечислены вместе со значениями в открывающем теге. Текст внутри тегов составляет текстовый элемент. ```sql => SELECT $xml$ Intel® Core™ i7-7567U Crucial DDR4-2400 SODIMM Intel 760p Series Toshiba Canvio $xml$ AS xml \gset ``` В PostgreSQL имеется тип xml, значение которого хранится в виде символьной строки, а при приведении к типу происходит проверка структуры. В зависимости от значения параметра xmloption проверка ожидает: document — документ (с одним корнем), content — фрагмент документа (любой набор элементов). ```text => SHOW xmloption; ``` ```text xmloption ----------- content (1 row) ``` При приведении к типу xml форматирование сохраняется: ```sql => SELECT :'xml'::xml; ``` ```text xml -------------------------------------------------------------------- + + + Intel® Core™ i7-7567U + + + Crucial DDR4-2400 SODIMM + + + + Intel 760p Series + Toshiba Canvio + + + (1 row) ``` А попытка привести некорректный фрагмент даст ошибку: ```sql => SELECT 'wrong xml'::xml; ``` ## Page 9: XML: выражения XPath {#source-page-009} ```sql ERROR: invalid XML content LINE 1: SELECT 'wrong xml'::xml; ^ DETAIL: line 1: Premature end of data in tag tag line 1 wrong xml ^ ``` Посмотрим, какие средства есть для получения части XML-документа. Для этого используются выражения языка запросов XPath 1.0. Мы не будем детально рассматривать все возможности XPath, но посмотрим некоторые примеры. XML состоит из иерархии элементов, поэтому язык описывает перемещения по дереву. Часть документа, соответствующая пути от корня: ```sql => SELECT xpath('/computer/motherboard/ram', :'xml'); ``` ```text xpath ------------------------------------------------------------ {" + + Crucial DDR4-2400 SODIMM+ "} (1 row) ``` В пути можно указывать не только непосредственные потомки: ```sql => SELECT xpath('/computer//ram', :'xml'); ``` ```text xpath ------------------------------------------------------------ {" + + Crucial DDR4-2400 SODIMM+ "} (1 row) ``` По дереву элементов можно «двигаться» не только вниз к листьям, но и вверх к корню: ```sql => SELECT xpath('//ram/dimm/..', :'xml'); ``` ```text xpath ------------------------------------------------------------ {" + + Crucial DDR4-2400 SODIMM+ "} (1 row) ``` Вместо конкретного имени элемента можно указать, что подходит любой: ```sql => SELECT xpath('//disks/*', :'xml') \gx ``` ```text -[ RECORD 1 ]----------------------------------------------------------------------------- xpath | {"Intel 760p Series","Toshiba Canvio"} ``` Здесь мы получили в массиве две части XML-документа. Можно посчитать количество: ```sql => SELECT xpath('count(//disks/*)', :'xml'); ``` ```text xpath ------- {2} (1 row) ``` Можно извлечь не весь элемент, а только его текст: ```sql => SELECT xpath('//cpu/text()', :'xml'); ``` ```text xpath --------------------------- {"Intel® Core™ i7-7567U"} (1 row) ``` Атрибуты записываются с помощью «собаки». Найдем значения атрибутов size\_gb: ## Page 10: Page 10 {#source-page-010} ```sql => SELECT xpath('//@size_gb', :'xml'); ``` ```text xpath --------------- {32,512,3000} (1 row) ``` Условия фильтрации записываются в квадратных скобках. Найдем все элементы, объем памяти которых начинается от 1000 гигабайт: ```sql => SELECT xpath('//*[@size_gb >= 1000]', :'xml'); ``` ```text xpath ------------------------------------------------ {"Toshiba Canvio"} (1 row) ``` Сравнение работает, потому что XPath поддерживает числовые типы. Кроме этого, поддерживаются строки и логический тип. Выражения XPath применяются не только в функции xpath, но и в других. Например, можно проверить, содержит ли XML-документ указанный фрагмент: ```sql => SELECT xmlexists( '//disks/*[starts-with(text(),''Toshiba'')]' PASSING :'xml' ); ``` ```text xmlexists ----------- t (1 row) ``` ```sql => SELECT xmlexists( '//disks/*[starts-with(text(),''Seagate'')]' PASSING :'xml' ); ``` ```text xmlexists ----------- f (1 row) ``` ### XML: преобразование в реляционный вид и обратно Для того чтобы преобразовать XML-документ к реляционному (табличному) виду, используется функция xmltable. Пусть у нас имеется таблица для дисковых накопителей: ```sql => CREATE TABLE disks ( drive_type text, name text, capacity integer ); ``` ```sql CREATE TABLE ``` Сначала выделим нужную часть документа: ```sql => SELECT xpath('//disks/*', :'xml'); ``` ```text xpath ------------------------------------------------------------------------------------------ ----- {"Intel 760p Series","Toshiba Canvio"} (1 row) ``` Теперь можно написать вызов функции xmltable. В нем мы указываем выражение XPath, сам документ, а также описываем, как получать значения для столбцов таблицы с помощью дополнительных XPath-выражений: ## Page 11: Page 11 {#source-page-011} ```sql => SELECT * FROM xmltable( '//disks/*' PASSING :'xml' COLUMNS drive_type text PATH 'name()', ``` ```text name text PATH 'text()', capacity integer PATH '@size_gb * 1024' ); ``` ```text drive_type | name | capacity ------------+-------------------+---------- ssd | Intel 760p Series | 524288 hdd | Toshiba Canvio | 3072000 (2 rows) ``` Обратите внимание: выражения XPath для столбцов отсчитываются не от корня документа, а от текущего контекста (элемента, найденного основным выражением XPath); в выражениях можно использовать некоторые арифметические операции. Результат такого запроса можно непосредственно вставить в таблицу: ```sql => INSERT INTO disks(drive_type, name, capacity) SELECT * FROM xmltable( '//disks/*' PASSING :'xml' COLUMNS drive_type text PATH 'name()', name text PATH 'text()', capacity integer PATH '@size_gb * 1024' ); ``` ```sql INSERT 0 2 ``` Для создания документов XML имеется довольно много функций, с помощью которых можно собрать документ «по кусочкам». Но есть также функции, позволяющие выгрузить в XML целую таблицу, или результат запроса, или даже всю базу данных в фиксированном формате. ```sql => SELECT table_to_xml( tbl => 'disks', nulls => true, -- выводить столбцы с NULL tableforest => false, -- в корне один элемент targetns => '' -- пространство имен XML не нужно ); ``` ```text table_to_xml --------------------------------------------------------------- + + + ssd + Intel 760p Series + 524288 + + + + hdd + Toshiba Canvio + 3072000 + + + + (1 row) ``` ## Page 12: Формат JSON {#source-page-012} **JavaScript Object Notation** - простой язык разметки, стандарт 1999 года - появился в JavaScript, но распространен повсеместно - Структура - объекты (пары «ключ: значение»), массивы значений - значения текстовые, числовые, даты, логические - Инструментарий - PostgreSQL: язык запросов JSONPath, - неполная пока поддержка стандарта SQL/JSON, - индексирование ### Комментарии преподавателя Формат JSON — подмножество языка JavaScript, описывающее: ● простые значения (текстовые, числовые, даты и логические); ● объекты, состоящие из пар «ключ — значение»; ● массивы значений. В двух последних случаях значения могут быть как простыми, так и объектами или массивами, то есть документ имеет иерархическую структуру. Несмотря на то, что JSON — «родной» формат для JavaScript, он получил огромное распространение благодаря своей простоте и удобству, и повсеместно используется в современном интернете. Изначально PostgreSQL предоставлял собственные операции для работы с JSON; при необходимости более серьезного инструментария можно было воспользоваться расширением jsQuery. В настоящее время в PostgreSQL реализована часть стандарта SQL/JSON:2016, включая язык запросов JSONPath. Имеется и индексирование документов JSON. Это позволяет комбинировать реляционные возможности PostgreSQL с возможностями, предоставляемыми NoSQL-базами. ## Page 13: Типы данных для JSON {#source-page-013} **Json** - хранение в виде обычного текста + синтаксическая проверка - сохраняется исходное форматирование - необходимость повторного разбора при каждом запросе - Jsonb - внутренний формат, исключающий повторный разбор - поддержка SQL/JSON (язык запросов JSONPath) - поддержка индексирования ### Комментарии преподавателя Имеются два типа данных для представления документов JSON: json и jsonb. Первый появился раньше и, по сути, просто хранит документ в виде текстовой строки (при этом, конечно, проверяется, что строка является корректным документом JSON). Но при любом обращении к части документа JSON его приходится заново разбирать. Это вызывает большие накладные расходы. Кроме того, для формата json не реализован язык JSONPath и не работает индексирование. Поэтому json применяется редко. Обычно используется тип jsonb (b — binary). Этот формат сохраняет однажды разобранную иерархию элементов, что позволяет эффективно работать с документом. Следует учитывать, что исходный вид документа при этом не сохраняется: нарушается порядок следования элементов, пропадают отступы и дублирующиеся ключи. ATH ## Page 14: JSON: типы данных json и jsonb {#source-page-014} Документ, описывающий компоненты компьютера, может выглядеть в JSON так: ```sql => SELECT $js$ { "motherboard": { "cpu": "Intel® Core™ i7-7567U", "ram": [ { "type": "dimm", "size_gb": 32, "model": "Crucial DDR4-2400 SODIMM" } ] }, "disks": [ { "type": "ssd", "size_gb": 512, "model": "Intel 760p Series" }, { "type": "hdd", "size_gb": 3000, "model": "Toshiba Canvio" } ] } $js$ AS json \gset ``` Тип json хранит документ как обычный текст: ```sql => SELECT :'json'::json; ``` ```text json --------------------------------------------- + { "motherboard": { + "cpu": "Intel® Core™ i7-7567U", + "ram": [ + { "type": "dimm", + "size_gb": 32, + "model": "Crucial DDR4-2400 SODIMM"+ } + ] + }, + "disks": [ + { "type": "ssd", + "size_gb": 512, + "model": "Intel 760p Series" + }, + { "type": "hdd", + "size_gb": 3000, + "model": "Toshiba Canvio" + } + ] + } + (1 row) ``` В jsonb документ разбирается и записывается во внутреннем формате, сохраняющем структуру разбора. Из-за этого при выводе документ составляется заново в эквивалентном, но ином виде: ```sql => SELECT :'json'::jsonb \gx ``` ```text -[ RECORD 1 ]----------------------------------------------------------------------------- jsonb | {"disks": [{"type": "ssd", "model": "Intel 760p Series", "size_gb": 512}, {"type": "hdd", "model": "Toshiba Canvio", "size_gb": 3000}], "motherboard": {"cpu": "Intel® Core™ i7-7567U", "ram": [{"type": "dimm", "model": "Crucial DDR4-2400 SODIMM", "size_gb": 32}]}} ``` Чтобы вывести документ в человекочитаемом виде, можно использовать специальную функцию: ```sql => SELECT jsonb_pretty(:'json'::jsonb); ``` ## Page 15: Page 15 {#source-page-015} ```text jsonb_pretty ------------------------------------------------------ { + "disks": [ + { + "type": "ssd", + "model": "Intel 760p Series", + "size_gb": 512 + }, + { + "type": "hdd", + "model": "Toshiba Canvio", + "size_gb": 3000 + } + ], + "motherboard": { + "cpu": "Intel® Core™ i7-7567U", + "ram": [ + { + "type": "dimm", + "model": "Crucial DDR4-2400 SODIMM",+ "size_gb": 32 + } + ] + } + } (1 row) ``` Дальше мы будем работать с типом jsonb, который предоставляет больше возможностей. ### JSON: выражения JSONPath и другие средства Для получения части JSON-документа стандарт SQL:2016 определил язык запросов JSONPath. Вот некоторые примеры. Так же, как и XPath, JSONPath позволяет спускаться по дереву элементов. Часть документа, соответствующая пути от корня: ```sql => SELECT jsonb_pretty(jsonb_path_query(:'json', '$.motherboard.ram')); ``` ```text jsonb_pretty ---------------------------------------------- [ + { + "type": "dimm", + "model": "Crucial DDR4-2400 SODIMM",+ "size_gb": 32 + } + ] (1 row) ``` Элементы массива указываются в квадратных скобках: ```sql => SELECT jsonb_pretty(jsonb_path_query(:'json', '$.disks[0]')); ``` ```text jsonb_pretty ----------------------------------- { + "type": "ssd", + "model": "Intel 760p Series",+ "size_gb": 512 + } (1 row) ``` Можно получить и все элементы сразу: ```sql => SELECT jsonb_pretty(jsonb_path_query(:'json', '$.disks[*]')); ``` ## Page 16: Page 16 {#source-page-016} ```text jsonb_pretty ----------------------------------- { + "type": "ssd", + "model": "Intel 760p Series",+ "size_gb": 512 + } { + "type": "hdd", + "model": "Toshiba Canvio", + "size_gb": 3000 + } (2 rows) ``` Условия фильтрации записываются в скобках после вопросительного знака. Символ @ обозначает текущий путь. Найдем диски, объем памяти которых начинается от 1000 гигабайт: ```sql => SELECT jsonb_pretty( jsonb_path_query(:'json', '$.disks ? (@.size_gb > 1000)') ); ``` ```text jsonb_pretty -------------------------------- { + "type": "hdd", + "model": "Toshiba Canvio",+ "size_gb": 3000 + } (1 row) ``` Условия являются частью пути, который можно продолжить дальше. Выберем только модель: ```sql => SELECT jsonb_pretty( jsonb_path_query(:'json', '$.disks ? (@.size_gb > 1000).model') ); ``` ```text jsonb_pretty ------------------ "Toshiba Canvio" (1 row) ``` В пути может быть и несколько условий: ```sql => SELECT jsonb_pretty( jsonb_path_query( :'json', '$.disks ? (@.size_gb > 128).model ? (@ starts with "Intel")' ) ); ``` ```text jsonb_pretty --------------------- "Intel 760p Series" (1 row) ``` Кроме средств JSONPath, можно применять и «традиционную» стрелочную нотацию. Переходим к ключу motherboard, затем к ключу ram, затем берем первый (нулевой) элемент массива: ```sql => SELECT jsonb_pretty( (:'json'::jsonb)->'motherboard'->'ram'->0 ); ``` ```text jsonb_pretty ------------------------------------------ { + "type": "dimm", + "model": "Crucial DDR4-2400 SODIMM",+ "size_gb": 32 + } (1 row) ``` Двойная стрелка возвращает не jsonb, а текстовое представление (необходимые фильтрации придется выполнять уже на уровне SQL): ```sql => SELECT (:'json'::jsonb)->'motherboard'->'ram'->0->>'model'; ``` ## Page 17: Page 17 {#source-page-017} ```text ?column? -------------------------- Crucial DDR4-2400 SODIMM (1 row) ``` Начиная с версии PostgreSQL 14 для работы c jsonb можно использовать индексную нотацию: ```sql => SELECT (:'json'::jsonb)['disks'][1]['model']; ``` ```text jsonb ------------------ "Toshiba Canvio" (1 row) ``` ### JSON: преобразование в реляционный вид и обратно Стандарт определяет функцию jsontable, но ее реализация ожидается только в PostgreSQL 17. Разумеется, можно выйти из положения и теми средствами, которые существуют. Сначала выделим все диски: ```text => TRUNCATE TABLE disks; ``` ```text TRUNCATE TABLE ``` ```sql => WITH dsk(d) AS ( SELECT jsonb_path_query(:'json', '$.disks[*]') ) SELECT d FROM dsk; ``` ```text d --------------------------------------------------------------- {"type": "ssd", "model": "Intel 760p Series", "size_gb": 512} {"type": "hdd", "model": "Toshiba Canvio", "size_gb": 3000} (2 rows) ``` На основе этого запроса несложно сделать вставку в таблицу: ```sql => INSERT INTO disks(drive_type, name, capacity) WITH dsk(d) AS ( SELECT jsonb_path_query(:'json', '$.disks[*]') ) SELECT d->>'type', d->>'model', (d->>'size_gb')::integer FROM dsk; ``` ```sql INSERT 0 2 ``` Для обратного преобразования удобно воспользоваться функцией row\_to\_json: ```sql => SELECT row_to_json(disks) FROM disks; ``` ```text row_to_json ---------------------------------------------------------------- {"drive_type":"ssd","name":"Intel 760p Series","capacity":512} {"drive_type":"hdd","name":"Toshiba Canvio","capacity":3000} (2 rows) ``` Соединить строки в общий JSON-массив можно, например, так: ```sql => SELECT json_agg(disks) FROM disks; ``` ```text json_agg ------------------------------------------------------------------- [{"drive_type":"ssd","name":"Intel 760p Series","capacity":512}, + {"drive_type":"hdd","name":"Toshiba Canvio","capacity":3000}] (1 row) ``` Здесь отдельные строки преобразуются в объекты JSON автоматически. ## Page 18: Метод доступа GIN {#source-page-018} ![Source slide page 18: Метод доступа GIN](images/page-018.png){#source-figure-018 caption="Source slide page 18: Метод доступа GIN"} **Инвертированный индекс** - метод применим для сложносоставных значений (массив, текст) - API метода доступа определяет, как выделять элементы значения ### Комментарии преподавателя Идея метода доступа GIN (Generalized Inverted Index) основана на том, что для сложносоставных значений имеет смысл использовать в качестве ключей элементы значений, а не значения целиком. Представьте предметный указатель в конце книги. Страницы книги — сложносоставные значения (текст), а указатель позволяет ответить на вопрос «на каких страницах встречается такой-то термин?». Для хранения элементов в GIN используется обычное B-дерево, поэтому элементы должны принадлежать к сортируемому типу данных. Основные отличия от B-дерева состоят в следующем: ● Когда нужно проиндексировать новое значение, это значение разбивается на элементы и индексируются сразу все элементы. Поэтому в индекс добавляется не один элемент, а сразу несколько (обычно много). ● Каждый элемент индекса ссылается на множество табличных строк. ● Хотя элементы и организованы в B-дерево, классы операторов GIN не поддерживают операции сравнения «больше», «меньше». Таким образом, GIN оптимизирован для других условий использования, нежели B-дерево. (Но имеется расширение btree\_gin, реализующее классы операторов GIN для обычных типов данных.) ## Page 19: Метод доступа GIN для индексирования JSON {#source-page-019} Пусть теперь таблица с дисками будет содержать JSON-документы. ```sql => DROP TABLE disks; ``` ```sql DROP TABLE ``` ```sql => CREATE TABLE disks( id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY, disk jsonb ); ``` ```sql CREATE TABLE ``` Заполним ее разными моделями, от 10 до 1000 Гбайт: ```sql => INSERT INTO disks(disk) WITH rnd(r) AS ( SELECT (10+random()*990)::integer FROM generate_series(1,100_000) ), dsk(type, model, capacity, plates) AS ( SELECT 'hdd', 'NoName '||r||' GB', r, (1 + random()*9)::integer FROM rnd ) SELECT row_to_json(dsk) FROM dsk; ``` ```sql INSERT 0 100000 ``` ```text => ANALYZE disks; ``` ```text ANALYZE ``` Вот что получилось: ```sql => SELECT * FROM disks LIMIT 3; ``` ```text id | disk ----+------------------------------------------------------------------------- 1 | {"type": "hdd", "model": "NoName 869 GB", "plates": 3, "capacity": 869} 2 | {"type": "hdd", "model": "NoName 128 GB", "plates": 4, "capacity": 128} 3 | {"type": "hdd", "model": "NoName 734 GB", "plates": 1, "capacity": 734} (3 rows) ``` Сколько всего моделей имеют емкость 10 Гбайт и сколько времени займет поиск? ```text => \timing on ``` ```text Timing is on. ``` Оператор @? проверяет, есть ли в документе JSON заданный путь. ```sql => SELECT count(*) FROM disks WHERE disk @? '$ ? (@.capacity == 10)'; ``` ```text count ------- 50 (1 row) ``` ```text Time: 78,916 ms ``` ```text => \timing off ``` ```text Timing is off. ``` Как выполняется этот запрос? ```sql => EXPLAIN (costs off) SELECT count(*) FROM disks WHERE disk @? '$ ? (@.capacity == 10)'; ``` ```text QUERY PLAN -------------------------------------------------------------- Aggregate -> Seq Scan on disks Filter: (disk @? '$?(@."capacity" == 10)'::jsonpath) (3 rows) ``` Конечно, используется полное сканирование таблицы — у нас нет подходящего индекса. ## Page 20: Page 20 {#source-page-020} Документы JSONB можно индексировать с помощью метода GIN. Для этого есть два доступных класса операторов: ```text => \dAc gin jsonb ``` ```text List of operator classes AM | Input type | Storage type | Operator class | Default? -----+------------+--------------+----------------+---------- gin | jsonb | text | jsonb_ops | yes gin | jsonb | integer | jsonb_path_ops | no (2 rows) ``` Класс по умолчанию, jsonb\_ops, более универсален, но менее эффективен. Этот класс операторов помещает в индекс все ключи и значения. Из-за этого поиск получается неточным: значение 10 может быть найдено не только в контексте емкости (ключ capacity), но и как число пластин (ключ plates). Зато такой индекс поддерживает и другие операции с JSONB. Попробуем. ```sql => CREATE INDEX disks_json_idx ON disks USING gin(disk); ``` ```sql CREATE INDEX ``` ```text => \timing on ``` ```text Timing is on. ``` ```sql => SELECT count(*) FROM disks WHERE disk @? '$ ? (@.capacity == 10)'; ``` ```text count ------- 50 (1 row) ``` ```text Time: 15,747 ms ``` ```text => \timing off ``` ```text Timing is off. ``` Доступ, тем не менее, ускоряется. ```sql => EXPLAIN (costs off) SELECT count(*) FROM disks WHERE disk @? '$ ? (@.capacity == 10)'; ``` ```text QUERY PLAN ------------------------------------------------------------------------ Aggregate -> Bitmap Heap Scan on disks Recheck Cond: (disk @? '$?(@."capacity" == 10)'::jsonpath) -> Bitmap Index Scan on disks_json_idx Index Cond: (disk @? '$?(@."capacity" == 10)'::jsonpath) (5 rows) ``` Другой класс операторов, jsonb\_path\_ops, помещает в индекс значения вместе с путем, который к ним ведет. За счет этого поиск становится более точным, хотя поддерживаются не все операции. Проверим и этот способ: ```sql => CREATE INDEX disks_json_path_idx ON disks USING gin(disk jsonb_path_ops); ``` ```sql CREATE INDEX ``` ```text => \timing on ``` ```text Timing is on. ``` ```sql => SELECT count(*) FROM disks WHERE disk @? '$ ? (@.capacity == 10)'; ``` ```text count ------- 50 (1 row) ``` ```text Time: 1,178 ms ``` ```text => \timing off ``` ```text Timing is off. ``` Так гораздо лучше. Еще один вариант — построить индекс на основе B-дерева по выражению. Вот так: ## Page 21: Page 21 {#source-page-021} ```sql => CREATE INDEX disks_btree_idx ON disks( (disk->>'capacity') ); ``` ```sql CREATE INDEX ``` ```text => \timing on ``` ```text Timing is on. ``` ```sql => SELECT count(*) FROM disks WHERE disk->>'capacity' = '10'; ``` ```text count ------- 50 (1 row) ``` ```text Time: 1,293 ms ``` ```text => \timing off ``` ```text Timing is off. ``` Но такой способ, конечно, менее универсален — под каждый запрос потребуется создавать отдельный индекс. Сравним размер индексов (для сравнения выводится и размер таблицы): ```sql => SELECT indexname, pg_size_pretty(pg_total_relation_size(indexname::regclass)) FROM pg_indexes WHERE tablename = 'disks' UNION ALL SELECT 'disks', pg_size_pretty(pg_table_size('disks'::regclass)); ``` ```text indexname | pg_size_pretty ---------------------+---------------- disks_pkey | 2208 kB disks_json_idx | 1904 kB disks_json_path_idx | 1408 kB disks_btree_idx | 712 kB disks | 13 MB (5 rows) ``` ## Page 22: Итоги {#source-page-022} **PostgreSQL поддерживает слабоструктурированные типы** - Частичная поддержка XML - формат теряет популярность - Полноценная поддержка JSON - направление активно развивается - Индексирование на основе метода доступа GIN ## Page 23: Практика {#source-page-023} 1. Часть сведений о книгах (ISBN, аннотация и другие) выводятся приложением только на отдельной странице. Сейчас эти данные хранятся в таблице books, но не передаются приложению. Измените функцию, реализующую API приложения, так, чтобы сведения передавались в виде одного объекта JSON. Проверьте реализацию в приложении. 2. Многочисленные столбцы, хранящие дополнительные сведения о книгах, никак не используются в серверной части приложения. Замените их на один столбец, в котором сведения будут храниться в формате JSON. Сделайте это прозрачно для приложения. ### Комментарии преподавателя 1. Сведения должны передаваться в объекте JSON следующего формата: \{ "ISBN": isbn, "Аннотация": abstract, "Издательство": publisher, "Год выпуска": year, "Гарнитура": typeface, "Издание": edition, "Серия": series \} 2. Действуйте в предположении, что во время замены столбцов приложение работает и должно корректно выполнять запросы пользователей. Не забудьте, что вместе с заменой столбцов на один общий необходимо исправить и интерфейсную функцию приложения. Кроме того, считайте, что одновременно с работой приложения могут изменяться как данные о существующих книгах, так и добавляться новые книги. ## Page 24: 1. Дополнительные сведения о книгах {#source-page-024} Приложение рассчитывает получить дополнительные сведения о книгах в столбце additional таблицы, возвращаемой вызовом webapi.get\_catalog, которая, в свою очередь, обращается за данными к public.get\_catalog. Последняя получает дополнительные атрибуты через отдельную функцию, которая сейчас возвращает пустой документ: ```text => \sf get_additional ``` ```sql CREATE OR REPLACE FUNCTION public.get_additional(book books) RETURNS jsonb LANGUAGE sql IMMUTABLE RETURN NULL::jsonb ``` Изменим функцию следующим образом: ```sql => CREATE OR REPLACE FUNCTION public.get_additional(book books) RETURNS jsonb LANGUAGE sql STABLE RETURN jsonb_build_object( 'ISBN', book.isbn, 'Аннотация', book.abstract, 'Издательство', book.publisher, 'Год выпуска', book.year, 'Гарнитура', book.typeface, 'Издание', book.edition, 'Серия', book.series ); ``` ```sql CREATE FUNCTION ``` ### 2. Замена нескольких столбцов на один формата JSON Добавим к таблице books необходимый столбец: ```sql => ALTER TABLE books ADD additional jsonb; ``` ```sql ALTER TABLE ``` Если данные о книгах могут изменяться в процессе наших действий, мы можем создать триггер, который будет переносить изменения в новый столбец: ```sql => CREATE FUNCTION fill_additional() RETURNS trigger AS $$ BEGIN NEW.additional := jsonb_build_object( 'ISBN', NEW.isbn, 'Аннотация', NEW.abstract, 'Издательство', NEW.publisher, 'Год выпуска', NEW.year, 'Гарнитура', NEW.typeface, 'Издание', NEW.edition, 'Серия', NEW.series ); RETURN NEW; END; $$ LANGUAGE plpgsql; ``` ```sql CREATE FUNCTION ``` ```sql => CREATE TRIGGER books_additional BEFORE INSERT OR UPDATE OF isbn, abstract, publisher, year, typeface, edition, series ON books FOR EACH ROW EXECUTE FUNCTION fill_additional(); ``` ```sql CREATE TRIGGER ``` Теперь перенесем в новый столбец исторические данные: ```sql => UPDATE books SET additional = get_additional(books) WHERE additional IS NULL; ``` ```sql UPDATE 100 ``` Начиная с этого момента старые столбцы менять нельзя. Если бы у нас была процедура загрузки новых поступлений, ее ## Page 25: Page 25 {#source-page-025} следовало бы переделать на заполнение нового столбца. Переключим приложение на использование нового столбца: ```sql => CREATE OR REPLACE FUNCTION public.get_additional(book books) RETURNS jsonb LANGUAGE sql STABLE RETURN book.additional; ``` ```sql CREATE FUNCTION ``` Теперь можно удалить временный триггер: ```sql => DROP TRIGGER books_additional ON books; ``` ```sql DROP TRIGGER ``` ```sql => DROP FUNCTION fill_additional; ``` ```sql DROP FUNCTION ``` И можно удалить ненужные теперь столбцы таблицы: ```sql => ALTER TABLE books DROP isbn, DROP abstract, DROP publisher, DROP year, DROP typeface, DROP edition, DROP series; ``` ```sql ALTER TABLE ``` Удаление столбцов происходит быстро, поскольку данные фактически не удаляются, а только становятся невидимыми. Поэтому размер таблицы не уменьшится до тех пор, пока строки не будут физически перезаписаны, например, при обновлениях. ## Page 26: Практика+ {#source-page-026} 1. В базе данных имеются таблицы пользователей и их заказов, связанные отношением «один ко многим». Напишите функцию, которая по идентификатору пользователя возвращает документ JSON, содержащий все сведения о пользователе вместе с массивом его заказов. 2. В пользовательском интерфейсе необходимо выводить названия городов, хранящиеся в базе данных, на выбранном языке. Один из способов — хранить все переводы названий в объекте JSON в виде пар «код языка — название». Реализуйте такой способ. Для удобства создайте представление, показывающее название городов на языке, заданном в конфигурационном параметре сервера. ### Комментарии преподавателя 1. Таблицы могут быть следующими: CREATE TABLE users( id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY, name text); ```sql CREATE TABLE orders( id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY, user_id integer REFERENCES users(id), amount numeric); ``` Результат должен иметь вид: \{ "user\_id": ..., "name": ..., "orders": [ \{ "order\_id": ..., "amount": ... \}, ... ] \} Для того чтобы сформировать объект JSON из нескольких полей, воспользуйтесь функцией jsonb\_build\_object. 2. Название пользовательского конфигурационного параметра должно содержать точку в своем имени, например, «translation.lang». Получить текущее значение параметра можно функцией current\_setting. ## Page 27: 1. Формирование JSON {#source-page-027} ```sql => CREATE DATABASE ext_semistruct; ``` ```sql CREATE DATABASE ``` ```sql => \c ext_semistruct ``` ```text You are now connected to database "ext_semistruct" as user "student". ``` Создадим таблицы и тестовые данные: ```sql => CREATE TABLE users( id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY, name text ); ``` ```sql CREATE TABLE ``` ```sql => CREATE TABLE orders( id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY, user_id integer REFERENCES users(id), amount numeric ); ``` ```sql CREATE TABLE ``` ```sql => INSERT INTO users(name) VALUES ('alice'), ('bob'), ('charlie'); ``` ```sql INSERT 0 3 ``` ```sql => INSERT INTO orders(amount, user_id) SELECT round( (random()*1000)::numeric, 2), u.id FROM users u, generate_series(1,3); ``` ```sql INSERT 0 9 ``` Функция использует jsonb\_build\_object для создания объекта и jsonb\_agg для создания массива: ```sql => CREATE FUNCTION get_users_w_orders(user_id integer) RETURNS jsonb LANGUAGE sql STABLE RETURN (SELECT jsonb_build_object( 'user_id', u.id, 'name', u.name, 'orders', jsonb_agg(jsonb_build_object( 'order_id', o.id, 'amount', o.amount )) ) FROM users u JOIN orders o ON o.user_id = u.id WHERE u.id = get_users_w_orders.user_id GROUP BY u.id); ``` ```sql CREATE FUNCTION ``` Проверим: ```sql => SELECT jsonb_pretty(get_users_w_orders(1)); ``` ## Page 28: Page 28 {#source-page-028} ```text jsonb_pretty ------------------------------- { + "name": "alice", + "orders": [ + { + "amount": 851.55,+ "order_id": 1 + }, + { + "amount": 884.08,+ "order_id": 4 + }, + { + "amount": 177.39,+ "order_id": 7 + } + ], + "user_id": 1 + } (1 row) ``` ### 2. Хранение переводов Таблица с городами: ```sql => CREATE TABLE cities_ml( id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY, data jsonb ); ``` ```sql CREATE TABLE ``` ```sql => INSERT INTO cities_ml(data) VALUES ('{ "ru": "Москва", "en": "Moscow", "it": "Mosca" }'), ('{ "ru": "Санкт-Петербург", "en": "Saint Petersburg", "it": "San Pietroburgo" }'); ``` ```sql INSERT 0 2 ``` Представление может быть устроено следующим образом: ```sql => CREATE VIEW cities AS SELECT c.id, c.data ->> current_setting( 'translation.lang', /* missing_ok */true ) AS name FROM cities_ml c; ``` ```sql CREATE VIEW ``` Проверим результат с разными значениями параметра: ```text => SET translation.lang = 'ru'; ``` ```text SET ``` ```sql => SELECT * FROM cities; ``` ```text id | name ----+----------------- 1 | Москва 2 | Санкт-Петербург (2 rows) ``` ```text => SET translation.lang = 'en'; ``` ```text SET ``` ```sql => SELECT * FROM cities; ``` ```text id | name ----+------------------ 1 | Moscow 2 | Saint Petersburg (2 rows) ``` ## Page 29: Page 29 {#source-page-029} Таким образом приложение может установить язык один раз в начале сеанса, и затем запросы не будут зависеть от выбора пользователя.