Поддержка полей JSONB в СУБД PostgreSQL стала результатом большой работы команды PostgresPro и приблизила использование форматов и инструментов для работы с JSON в этой базе данных к статусу полноценной. В отличии от текстового в своей основе типа JSON, JSONB позволяет строить индексы по содержимому поля, что должно значительно ускорить поиск по таким данным. Также он реализует некоторые оптимизации, например не поддерживает дубликаты ключей в рамках одного уровня JSON-структуры, а если они все-таки встречаются, использует последнее значение.

В этой статье мы попробуем подключить и использовать функционал JSONB-полей в нашем java-приложении на фреймворке Jmix. Если вы используете Spring, решения по настройке и, может быть, даже использованию могут слегка отличаться, т. к. там используется ORM Hibernate.

Настройка проекта Jmix

Создадим новый проект с названием, например, jmixjsonb и настроим у него датасорс для использования СУБД PostgreSQL. Это можно сделать как из панели плагина Jmix, так и отредактировав первые строчки файла application.properties вручную.

Для обретения поддержки JSONB у полей наших сущностей, добавим JPA-конвертер и реализуем собственный тип данных. Для использования в Hibernate это делается аналогичным образом, но вместо отдельного типа данных используется аннотация конвертера над полем сущности.

package com.company.jmixjsonb.entity;

import elemental.json.Json;
import elemental.json.JsonObject;
import jakarta.persistence.AttributeConverter;
import jakarta.persistence.Converter;
import org.postgresql.util.PGobject;

import java.sql.SQLException;

@Converter(autoApply = true)
public class JsonbAttributeConverter implements AttributeConverter<JsonObject, PGobject> {

    @Override
    public PGobject convertToDatabaseColumn(JsonObject object) {
        if (object == null) return null;
        try {
            PGobject out = new PGobject();
            out.setType("jsonb");
            out.setValue(object.toJson());
            return out;
        } catch (SQLException ex) {
            throw new IllegalArgumentException("Cannot convert " + object + " to JSON", ex);
        }
    }

    @Override
    public JsonObject convertToEntityAttribute(PGobject value) {
        if (value == null) return null;

        return Json.parse(value.getValue());
    }
}

Методы конвертора определяют каким образом тип, полученный из контекста приложения, будет преобразован в подходящий для базы данных формат. В нем вы можете добавить каких-нибудь специфичных для собственной ситуации операций.

package com.company.jmixjsonb.entity;

import elemental.json.Json;
import elemental.json.JsonObject;
import io.jmix.core.metamodel.annotation.DatatypeDef;
import io.jmix.core.metamodel.annotation.Ddl;
import io.jmix.core.metamodel.datatype.Datatype;
import org.springframework.lang.Nullable;

import java.text.ParseException;
import java.util.Locale;

@DatatypeDef(
        id = "jsonb",
        javaClass = JsonObject.class,
        defaultForClass = true
)
@Ddl(dbms = "postgres", value = "jsonb")
public class JsonbDatatype implements Datatype<JsonObject> {

    @Override
    public String format(@Nullable Object value) {
        if (value instanceof JsonObject jsonObject) {
            return jsonObject.toJson();
        }
        return null;
    }

    @Override
    public String format(Object value, Locale locale) {
        return format(value);
    }

    @Override
    public JsonObject parse(@Nullable String value) throws ParseException {
        if (value == null)
            return null;
        return Json.parse(value);
    }

    @Override
    public JsonObject parse(String value, Locale locale) throws ParseException {
        return parse(value);
    }
}

Использование JSON-атрибутов

Теперь, когда у нас появился новый тип данных надо научиться его применять. Добавим сущность Product и определим у нее поле data.

@Column(name = "DATA_", columnDefinition = "jsonb")
private JsonObject data;

Для сущности сгенерируем CRUD-интерфейс оставив в визарде все параметры по умолчанию. Это можно сделать, нажав по кнопке Views из дизайнера сущности или из ветки User Interface навигационного дерева Jmix Studio.

Запустим проект. При запуске Jmix Studio предложит создать таблицы для СУБД, в которых наше поле будет создано с правильным типом данных.

Добавим продукту также поле name не забыв сгенерировать для него элементы управления.

При изменении сущностей проект надо перезапускать, в остальных случаях чаще всего Jmix Studio перезагрузит измененный код за вас «на горячую».

После успешного запуска приложения зайдем в инкогнито вкладке браузера на адрес http://localhost:8080 с логином и паролем admin.

Теперь мы можем добавить пару продуктов, а в поле data для них задать JSON с ключом tags и какими-нибудь значениями.

Открыв таблицу базы данных в просмотрщике, мы можем убедиться, что у нас подлинный тип jsonb, а не строковое поле или блоб.

Также это становится заметно если ввести в data невалидное с точки зрения формата JSON значение.

Запросы по JSON-данным

Для полной уверенности, что у нас все получилось можно попробовать запросы, с которыми справится только JSON-движок.

Запросы в контексте полей JSON структур заметно отличаются от стандартов SQL в своем синтаксисе, так что вы никогда их не перепутаете их с остальными.

Теперь добавим текстовое поле ввода в дескриптор product-list-view.xml прямо перед гридом и в Java-классе экрана инжектнем обработчик его изменения. Он будет выполнять нативный запрос в СУБД и подгружать результаты в грид при помощи дата контейнера.

@Subscribe("tagFilterField")
public void onTagFilterFieldComponentValueChange(final AbstractField.ComponentValueChangeEvent<TypedTextField<?>, ?> event) {
    List<Product> products = (List<Product>) entityManager
            .createNativeQuery("select * from product where data_ -> 'tags' @> '\"" +
                event.getValue() +
                "\"'::jsonb", Product.class).getResultList();
    productsDc.setItems(products);
}

Я по-разному пробовал, но у меня на таких запросах никак не получилось привязывать в запрос данные по-нормальному, т.е. через плейсхолдеры. Если вы знаете работающий на лайфхак, напишите в комментариях. Все варианты со стековерфлоу у меня не прокатили и в результате пришлось конкатенировать строку, хотя изобрести вариант с replace’ом собственного формата плейсхолдеров я, конечно, тоже же смог бы. Следует также помнить, что входные данные надо всегда обязательно экранировать, у нас эту задачу выполняет каст выражения в тип “jsonb”.

Следует обратить внимание на тот факт, что мы используем NativeQuery вместо традиционного для JPA JPQL потому, что нам требуются выражения в платформозависимом синтаксисе, который поддерживается только в СУБД PostgreSQL. Используя NativeQuery вы лишаетесь многих преимуществ, которые дает платформа Jmix. Таких как:

• Усиленная модель безопасности, которая автоматически фильтрует результаты в соответствии с ролями и атрибутами текущего авторизованного пользователя

• Встроенная поддержка пейджинации и лимитов в запросах и фильтров в гридах

• Переносимость результатов разработки между различными производителями СУБД

Так или иначе, теперь если что-то ввести в строке поиска и нажать Enter, грид обновит свои данные в соответствии с вводом, что можно считать успехом.

Можем ли мы использовать непосредственно массив для значений вместо объекта? Да, но в этом случае конвертер и дататайп будут несколько отличаться в реализациях.

package com.company.jmixjsonb.entity;

import elemental.json.Json;
import elemental.json.JsonArray;
import jakarta.persistence.AttributeConverter;
import jakarta.persistence.Converter;
import org.postgresql.util.PGobject;

import java.sql.SQLException;

@Converter(autoApply = true)
public class JsonbArrayAttributeConverter implements AttributeConverter<JsonArray, PGobject> {

    @Override
    public PGobject convertToDatabaseColumn(JsonArray object) {
        if (object == null) return null;
        try {
            PGobject out = new PGobject();
            out.setType("jsonb");
            out.setValue(object.toJson());
            return out;
        } catch (SQLException ex) {
            throw new IllegalArgumentException("Cannot convert " + object + " to JSON", ex);
        }
    }

    @Override
    public JsonArray convertToEntityAttribute(PGobject value) {
        if (value == null) return null;

        return Json.instance().parse(value.getValue());
    }
}

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

package com.company.jmixjsonb.entity;

import elemental.json.Json;
import elemental.json.JsonArray;
import io.jmix.core.metamodel.annotation.DatatypeDef;
import io.jmix.core.metamodel.annotation.Ddl;
import io.jmix.core.metamodel.datatype.Datatype;
import org.springframework.lang.Nullable;

import java.text.ParseException;
import java.util.Locale;

@DatatypeDef(
        id = "jsonbarray",
        javaClass = JsonArray.class,
        defaultForClass = true
)
@Ddl(dbms = "postgres", value = "jsonb")
public class JsonbArrayDatatype implements Datatype<JsonArray> {

    @Override
    public String format(@Nullable Object value) {
        if (value instanceof JsonArray jsonArray) {
            return jsonArray.toJson();
        }
        return null;
    }

    @Override
    public String format(Object value, Locale locale) {
        return format(value);
    }

    @Override
    public JsonArray parse(@Nullable String value) throws ParseException {
        if (value == null)
            return null;

        return Json.instance().parse(value);
    }

    @Override
    public JsonArray parse(String value, Locale locale) throws ParseException {
        return parse(value);
    }
}

Также поля сущности будут использовать тип JsonArray вместо JsonObject.

@Column(name = "TAGS")
private JsonArray tags;

В запросах можно использовать функции запросов JSONPath и все другие возможности интеграции JSON в PostgreSQL.

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

Также надо отметить, что в Jmix уже разработан аддон DynamicAttributes, который позволяет справляться с произвольными наборами данных без прямого изменения схемы таблиц и сущностей. Но использование JSON для хранения полей и их значений может упростить программную работу с такими данными. Особенно если они имеют техническое назначение и вариативный структурный состав и вам не требуется ручное управление ими через веб-интерфейс приложения или вы готовы дорабатывать для этого собственные интерфейсы. Кому-то этот подход может напомнить документальные NoSQL базы данных и соответствующие задачи.