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

Введение

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

Описание

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

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

Хорошей иллюстрацией может служить список достопримечательностей. Допустим, мы хотели бы хранить дополнительную информацию об интересных местах. Если мы возьмем архитектурную достопримечательность, то в формате 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 модели, помимо прочего, заключается в том, что мы можем эффективно работать с данными, не имеющими четкой структуры. Компактно хранить и лаконично писать запросы к таким данным. Добавлять новые ключи, без необходимости изменения схемы таблицы (что затратно).

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

² JSON формат, RFC 7159

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

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