null

JSON and PostgreSQL: Используем JSON чтобы сымитировать преимущества NoSQL-хранилища

Иногда вам нужна база данных NoSQL, но все, что у вас есть, это PostgreSQL

Разработчики ПО и дата-инженеры часто упускают из виду, встроенный во многие популярные БД тип данных, JSON. И порой зря, так как в некоторых ситуациях, возможность хранить JSON в таблице SQL - незаменимая вещь.
Тип данных JSON позволяет сочетать преимущества реляционной БД с гибкостью хранилища NoSQL.

Рассмотрим ситуацию, когда у нас есть веб-приложение, которое изначально спроектировано для работы со слоем данных SQL. Однако, впоследствии, возникает необходимость хранить и обрабатывать данные, поступающие из нестандартных источников. Например, данные могут загружаться пользователями из файлов CSV, которые не имеют заранее известную, чётко заданную схему.

Решение подобной задачи может стать проблемой для разработчиков. 
Например, можно написать динамические SQL-функции, которые будут создавать уникальные таблицы для каждой из загрузок CSV. Однако, очевидно, что это - громоздкое некрасивое решение, приводящее к сотням различных таблиц и к кошмару взаимодействия с ними.

В качестве альтернативы, можно интегрировать базу данных NoSQL. Но это не всегда возможно по разным причинам (технические ограничения, юридические моменты, чужая зона ответственности и пр.), либо сулит нежелательное увеличение сложности проекта и/или возникновение потенциального/реального технического долга. 

Одним из простых и доступных решений данной проблемы является использование для всех загрузок CSV одной таблицы, в которой содержимое файлов хранится в поле с типом данных JSON.

Существует два типа JSON, которые поддерживает PostgreSQL: JSON и JSONB

Тип данных JSON хранит данные в необработанном виде, - сохраняются пробелы между лексемами и порядок ключей (keys) в объекте JSON. Используйте это тип, если вам нужно сохранить данные в необработанном виде (raw form) и максимально быстро (так как отсутствуют какие-либо преобразования c данными)

Тип данных JSONB кодирует данные перед их сохранением. Кодирование данных позволяет выполнять индексацию и расширенные запросы. Используйте этот тип, если вам необходимо выполнять сложные запросы к данным или добавлять индексы к полю.

Тип JSONB - универсальный вариант

В примерах будет рассматриваться тип данных JSONB, так как он наиболее часто применяется на практике и покрывает большинство типовых сценариев использования.

И так, сперва создадим простую таблицу, содержащую поле JSONB:

create table jsonb_example (id serial, content jsonb);

Значения в поле c типом JSONB передаются в виде строк в формате JSON. База данных выдаст ошибку, если строки не соответствуют формату JSON.

Предположим, что в нашей таблице jsonb_example мы будем хранить данные о марках и моделях автомобилей. Заполним её некоторыми данными:

insert into jsonb_example(content)
values('{"manufacturer": "Toyota", "models": ["Camry", "Land Cruiser"]}'),
('{"manufacturer": "Hyundai", "models": ["Palisade", "Tucson"]}'),
('{"manufacturer": "Lada", "models": ["Vesta Cross", "XRAY"]}'); 

Для того, чтобы выбрать данные из поля JSON(B) в PostgreSQL используются следующие операторы:

-> (одиночная стрелочка) - возвращает данные из поля JSON в формате JSON
->> (двойная стрелочка) - возвращает данные из поля JSON в виде текста

Например, если нам нужно выбрать названия производителей, то запрос будет таким:

select id, content ->> 'manufacturer' as "Manufacturer Name" from jsonb_example;

id     Manufacturer Name
-----------------------------
1      Toyota
2      Hyundai
3      Lada 

Или же можно получить и производителя и соответствующие модели автомобилей:

select id, 
       content ->> 'manufacturer' as "Manufacturer Name",
       content ->> 'models' as "Car models"
from jsonb_example;

id     Manufacturer Name    Car models
---------------------------------------------------------
1      Toyota               ["Camry", "Land Cruiser"]
2      Hyundai              ["Palisade", "Tucson"]
3      Lada                 ["Vesta Cross", "XRAY"]

 

Объединение операторов JSON для извлечения вложенных данных

Для того чтобы извлечь данные, которые вложены в другие данные, мы можем объединять операторы JSON в цепочку. 
Чтобы продемонстрировать как это работает, добавим в таблицу данные JSON, содержащие вложенный объект:

insert into jsonb_example(content)
values('{"manufacturer": "Honda", "country_of_origin": {"name":"Japan", "population": "125.55 million"}}')

И так, для производителя Honda существует вложенный объект, описывающий страну происхождения производителя. 
Вот как запросить конкретные данные из этого вложенного объекта:

select id, content -> 'country_of_origin' ->> 'name' as "Country of Origin" 
from jsonb_example;

Результат:

id     Country of Origin 
---------------------------
1      
2      
3      
4      Japan

Обратите внимание, что, несмотря на то, что предыдущие строки для марок Toyota, Hyundai и Lada имеют другую схему JSON, запрос не выдал ошибку. Вместо этого, поле "Country of Origin" для этих строк пустое.

Также, обратите внимание, что для работы подобного запроса, первый оператор должен возвращать данные типа JSON, а не текстовое значение:

select id, content -> 'country_of_origin' ->> 'name' as "Country of Origin"..
-- будет работать так как первый оператор -> возвращает JSON

select id, content ->> 'Country of Origin' ->> 'name' as "Country of Origin"..
-- не будет работать так как первый оператор возвращает текстовое значение, 
-- которое не поддерживает дальнейшее использование операторов JSON

 

Использование операторов JSON в условиях WHERE

Вы можете использовать операторы JSON в условиях WHERE для выборки данных на основе требуемых Вам критериев.

Допустим, вы хотите выбрать строки, где имя производителя автомобилей содержит символ Y. Все, что для этого нужно - в условии WHERE извлечь нужное поле с помощью операторов JSON, а дальше - обычный SQL:

select id, content ->> 'manufacturer' as "Manufacturer Name"
from jsonb_example
WHERE content ->> 'manufacturer' like '%y%';

Результат:

id     Manufacturer Name
-----------------------------
1      Toyota
2      Hyundai

 

Операторы, специфические для типа JSONB 

Как упоминалось ранее, тип данных JSONB позволяет выполнять расширенные запросы. Это действительно так, потому что существует ряд операторов, которые работают только с этим типом данных.

К примеру:

`@>` и `<@` - два примера операторов, специфичных для типа JSONB.

Оператор @> проверяет, содержит ли значение JSON, расположенное перед оператором,
внутри себя значение JSON, расположенное после оператора. 

Например, следующий запрос вернет все строки, у которых в массиве моделей есть значения: Camry и Land Cruiser.

select * from jsonb_example
where content -> 'models' @> '["Camry", "Land Cruiser"]'

Примечание: этот запрос вернет ЛЮБУЮ строку, массив моделей которой содержит значения: Camry и Land Cruiser, включая строки, которые содержат дополнительные другие модели.

Полный список операторов JSON и их детальное описание, Вы можете посмотреть в официальной документации:

https://www.postgresql.org/docs/9.5/functions-json.html

Небольшой вывод

Поддержка JSON в PostgreSQL открывает ряд замечательных возможностей для хранения и работы с данными. Одна из них - хранение данных в полуструктурированном виде (не зная заранее, как именно эти данные будут выглядеть). Подобная гибкость открывает двери множеству вариантов использования: от хранения данных, возвращаемых из API, до хранения сложного, глубоко вложенного, созданного пользователем контента, который не соответствует фиксированной схеме.

 

Основано на труде "JSON and PostgreSQL: Using JSON to Mimic NoSQL’s Storage Benefits" автора Jon McEwen.