International conference of developers
and users of free / open source software

О Модели Данных SQL/JSON

Yury Andreev, St. Petersburg, Russian Federation

LVEE 2017

An SQL/JSON model is a part of SQL 2016 Standard which describes a JSON data model for SQL. It describes a jsonpath data type as a path language and nine functions to deal with JSON data. A new type of queries will be shown on a special branch of PostgreSQL.

Введение

В новою редакцию стандарта SQL1 была включена SQL/JSON модель. Это означает, что стандарт теперь предполагает работу со слабо-структурированными данными в JSON формате2. СУБД Postgres имеет долгую историю поддержки такого типа данных и теперь идет работа по поддержке стандарта4.

Описание

Для запросов в стандарте описаны девять новых функций, с префиксом JSON_ (JSON_{OBJECT, QUERY, …}), а также язык jsonpath для навигации по JSON. Это частичный аналог XPath для XML. Однако, в отличии от последнего, его синтаксис был продиктован уже имеющимися конструкциями в javascript. Выражение начинается со знака доллара,
переход к дочерним элементам осуществляется оператором ‘точка’.
(см. подробнее в 3). Путь передается во втором аргументе функций.

Пример использования

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


{
    "nearest_city": "city_name"
    "creator": "author_name"
    "style": "style_name"
    "date": {
        "start_centery": number
        "end_centery": number
    }
} 

В тоже время, для природных объектов бессмысленно указывать имя создателя или дату и могут понадобиться новые ключи: сведения о территории, высоте, глубине. С другой стороны, и те и другие могут содержать имя ближайшего населенного пункта, географическое расположение и прочее.

Записать информацию в базу мы можем с помощью функций конструкторов
JSON_OBJECT, JSON_ARRAY.

Если наши данные содержатся в колонке details, то запрос на наличие создателя (ключа creator) может выглядеть следующим образом:

SELECT 
    JSON_EXISTS(details, '$.creator') 
FROM table_name 
WHERE id = object_id;

А время начала строительства:

SELECT
    JSON_VALUE(details, '$.date.start_centery') 
FROM table_name
WHERE id = object_id;

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

Заключение

Преимущество JSON/SQL модели, помимо прочего, заключается в том, что мы можем эффективно работать с данными, не имеющими четкой структуры. Компактно хранить и лаконично писать запросы к таким данным. Добавлять новые ключи, без необходимости изменения схемы таблицы (что затратно).

1 Стандарт SQL, ISO/IEC 9075-2:2016

2 JSON формат, RFC 7159

3 О языке запросов jsonpath

4 Исходный код PostgreSQL, sqljson branch

Abstract licensed under Creative Commons Attribution-ShareAlike 3.0 license

Back