Чем тип JSONB отличается от JSON и почему это так важно для хранения и обработки данных гибкой структуры в Greenplum. Примеры SQL-запросов к JSON-данным и особенности синтаксиса JSONPath.
Чем JSONB отличается от JSON и почему это так важно?
Будучи основанной на PostgreSQL, Greenplum имеет множество аналогичных возможностей, включая поддержку работы с JSON-документами согласно стандарту RFC 7159. Для хранения JSON-документов в Greenplum есть 2 типа данных: JSON и JSONB, которые на вид практически одинаковы. Но JSONB имеет бинарную кодировку, а потому более эффективен для хранения данных. JSON не изменяет вводимый текст и хранит точную копию входного текста, что предполагает парсинг этих данных при каждом выполнении запроса. Внутри объектов JSON сохраняется семантически-незначимый пробел между токенами, а также порядок ключей. Все пары ключ/значение сохраняются, даже если JSON-объект содержит повторяющиеся ключи. В случае повторения ключей функции обработки JSON-данных в Greenplum считают последнее значение рабочим.
Тип данных JSONB изменяет вводимый текст, сохраняя его в декомпозированном двоичном формате. Это преобразование добавляет немного накладных расходов на ввод данных по сравнению с JSON, слегка замедляя его. Но функции обработки JSONB работают намного быстрее, поскольку повторная обработка данных не требуется. В JSONB не сохраняется пустое пространство (пробелы), порядок ключей объектов и дубли ключей объектов. В случае дублирующихся ключей в JSONB сохраняется только последнее значение. Наконец, JSONB поддерживает индексацию данных по GIN, B-дереву и хэш-индексам.
С учетом преимуществ бинарного формата JSONB над его текстовым вариантом, рекомендуется хранить JSON-документы с типом данных JSONB, если не нужно хранить порядок ключей объектов. Примечательно, что Greenplum допускает только одну кодировку набора символов для каждой базы данных. Поэтому типы JSON не могут строго соответствовать спецификации этого формата при кодировке базы данных, отличной от UTF-8. Не получится напрямую включить символы, которые не могут быть представлены в текущей кодировке базы данных, отличной от UTF-8. Стандарт спецификации RFC 7159 позволяет строкам JSON содержать escape-последовательности Unicode, обозначаемые \uXXXX. Greenplum для JSON-данных разрешает экранирование Unicode независимо от кодировки базы данных и проверяет это экранирование только на синтаксическую правильность (\u, за которым следуют четыре шестнадцатеричных цифры).
Для JSONB-данных функция ввода в Greenplum более строгая: не допускается экранирование Unicode для символов, отличных от ASCII в кодировке базы данных, отличной от UTF-8. JSONB также отклоняет \u0000, который не может быть представлен в текстовом типе данных, и требует, чтобы любое использование суррогатных пар Unicode для обозначения символов вне базовой плоскости было верным. Допустимые escape-последовательности Unicode, кроме \u0000, преобразуются для хранения в эквивалентный символ ASCII или UTF-8, что включает свертывание суррогатных пар в один символ.
Впрочем, многие функции обработки JSON-данных преобразуют escape-последовательности Unicode в обычные символы, а потому выдают те же самые типы ошибок, которые характерны для JSONB. Поэтому не рекомендуется смешивать escape-последовательности Unicode в JSON с кодировкой базы данных, отличной от UTF8.
При преобразовании JSON-текста в данные JSONB примитивные типы данных, описанные в RFC 7159, эффективно сопоставляются с собственными типами данных Greenplum, как показано в следующей таблице.
|
JSON |
Greenplum |
Примечание |
|
string |
text |
\u0000 не допускается. Escape-последовательности Unicode, отличные от ASCII, разрешены только для кодировки базы данных UTF-8 |
|
number |
numeric |
NaN и бесконечные значения запрещены |
|
boolean |
boolean |
Допускаются только строчные буквы true и false |
|
null |
(none) |
Тип JSON-примитива null отличается от типа NULL в SQL |
Есть некоторые незначительные ограничения реализацией RFC 7159 на допустимые данные JSONB, которые не применяются к типу данных JSON. К примеру, не получится преобразовать в JSONB число, выходящее за пределы диапазона числового типа Greenplum, тогда как с текстовым JSON таких проблем не возникнет. Также сложности могут возникать в других реализациях RFC 7159, поскольку обычно числовой примитивный тип JSON представляется как число с плавающей запятой двойной точности IEEE 754, что RFC 7159 явно допускает. Поэтому при использовании JSON в качестве формата обмена данными с другими системами есть риск потери числовой точности по сравнению с данными, изначально хранящимися в Greenplum.
В заключение отметим еще одну особенность типа данных JSONB: числа будут выводиться в соответствии с поведением базового числового типа. На практике это означает, что числа, введенные в экпоненциальном виде (с Е+/-), будут печататься без нее. Однако, тип данных JSONB сохраняет нули в конце дробной части, даже если они семантически несущественны.
Проектирование JSON-документов и SQL-запросы к ним в Greenplum
Представление данных в виде JSON-документов имеет более гибкую структуру, чем традиционная реляционная модель данных. Однако, даже для документов JSON рекомендуется иметь фиксированную структуру, поскольку это упрощает написание запросов к таким данным.
Данные типа JSON, как и любые другие данные Greenplum, хранящиеся в таблицах, находятся под контролем механизма параллельного доступа (MVCC). При том, что хранить большие JSON-документы возможно, при любом их изменении устанавливается блокировка всей строки. Поэтому для оптимизации блокировок транзакций, изменяющих данные, рекомендуется лимитировать размеры JSON-документов. В идеальном случае каждый JSON-документ должен собой представлять атомарный информационный блок, который нельзя разделить на более мелкие и индивидуально изменяемые фрагменты.
Примечательно, что JSONB позволяет оценивать вложенность структуры данных, в отличие от JSON, позволяя понять, есть ли внутри одного JSONB-документа другие. Для запросов к JSON-данным в Greenplum есть тип JSONPath, который обеспечивает двоичное представление проанализированного выражения пути SQL/JSON. JSONPath указывает элементы, которые извлекаются из данных JSON для дальнейшей обработки с помощью функций запроса SQL/JSON. Семантика предикатов и операторов JSONPath обычно соответствует SQL. Однако, чтобы обеспечить наиболее естественный способ работы с JSON-данными, синтаксис JSONPath использует некоторые соглашения JavaScript, такие как использование точки (.) для доступа к членам, квадратные скобки ([]) для массивам и индексация массивов с 0, в отличие от обычных массивов SQL, которые начинаются с 1.
В заключение рассмотрим таблицу с JSONB-данными:
CREATE TABLE my_films ( js jsonb );
INSERT INTO my_films VALUES (
'{ "favorites" : [
{ "kind" : "comedy", "films" : [
{ "title" : "Bananas",
"director" : "Woody Allen"},
{ "title" : "The Dinner Game",
"director" : "Francis Veber" } ] },
{ "kind" : "horror", "films" : [
{ "title" : "Psycho",
"director" : "Alfred Hitchcock" } ] },
{ "kind" : "thriller", "films" : [
{ "title" : "Vertigo",
"director" : "Alfred Hitchcock" } ] },
{ "kind" : "drama", "films" : [
{ "title" : "Yojimbo",
"director" : "Akira Kurosawa" } ] }
] }');
Следующий SQL-запрос будет считывать из таблицы my_films данные о фильмах и создавать представление, в котором жанр, название и режиссёр фильма распределяются по отдельным столбцам:
SELECT jt.* FROM
my_films,
JSON_TABLE ( js, '$.favorites[*]' COLUMNS (
id FOR ORDINALITY,
kind text PATH '$.kind',
NESTED PATH '$.films[*]' COLUMNS (
title text PATH '$.title',
director text PATH '$.director'))) AS jt;
Найти режиссёра, который снимал фильмы в двух разных жанрах, поможет следующий SQL-запрос:
SELECT
director1 AS director, title1, kind1, title2, kind2
FROM
my_films,
JSON_TABLE ( js, '$.favorites' AS favs COLUMNS (
NESTED PATH '$[*]' AS films1 COLUMNS (
kind1 text PATH '$.kind',
NESTED PATH '$.films[*]' AS film1 COLUMNS (
title1 text PATH '$.title',
director1 text PATH '$.director')
),
NESTED PATH '$[*]' AS films2 COLUMNS (
kind2 text PATH '$.kind',
NESTED PATH '$.films[*]' AS film2 COLUMNS (
title2 text PATH '$.title',
director2 text PATH '$.director'
)
)
)
PLAN (favs OUTER ((films1 INNER film1) CROSS (films2 INNER film2)))
) AS jt
WHERE kind1 > kind2 AND director1 = director2;
Узнайте больше подробностей про администрирование и эксплуатацию Greenplum с Arenadata DB для эффективного хранения и аналитики больших данных на специализированных курсах в нашем лицензированном учебном центре обучения и повышения квалификации для разработчиков, менеджеров, архитекторов, инженеров, администраторов, Data Scientist’ов и аналитиков Big Data в Москве:
Источники
