Complex Data Types - Array / JSON / JSONB

Support
,
#1

I’m running into a conceptual issue with complex data types (json, jsonb, array, etc), and how to store and make them filterable in jmix. I’d like to do the following but the existing documentation is very vague/requires me to have more knowledge than I do.

I have a complex data type such as json/jsonb/array that needs to store a single or combination of “tags” such as “HR” “Confidential” “Client Care” “Unemployment”, typical things we attach to notes on staff profiles. I’ve tried following the documentation on Jmix DataTypes as well as elsewhere on the forum How to insert a jsonb data type .

If I’m understanding this correctly, what I need to do to achieve this is to create a new data type such as “tag” that references a json/jsonb/array data type in my database (postgres), and then select that data type as my data type for my JPA entity file?

Does anyone have some suggestions on if I’m understanding this correctly, and how I would learn this? Also is this still filterable as a search parameter if I do it this way, or should I just make another references table if I need this to be a search filter?

Thank you for the help,
The struggling DBA / Now java dev lol.
Oran

#3

Hi Oran,

You can store and load complex database types if you define a JPA converter and a Jmix datatype for them. For example, to work with jsonb type, add the following classes to your project:

JPA converter:

package com.company.demo.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 JsonAttributeConverter 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());
    }
}

Jmix datatype:

package com.company.demo.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 = "json",
        javaClass = JsonObject.class,
        defaultForClass = true
)
@Ddl(dbms = "postgres", value = "jsonb")
public class JsonDatatype 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);
    }
}

Now you can define attributes of JsonObject type and map them to jsonb columns, for example:

@JmixEntity
@Table(name = "CUSTOMER")
@Entity
public class Customer {
// ...
    @Column(name = "PROPERTIES")
    private JsonObject properties;

    public JsonObject getProperties() {
        return properties;
    }

    public void setProperties(JsonObject properties) {
        this.properties = properties;
    }
}

Such an attribute can be directly displayed in UI if needed, in a data grid column or in a text field. But filtering by this field is hardly possible. To use out-of-the-box filtering features of Jmix (like genericFilter or propertyFilter components), the attribute must be of a simple type or an entity.

Regards,
Konstantin

#5

This makes a lot more sense now. I guess what I’m wondering from here is how do people manage multiple data types like an array or json in terms of something like a “tag” on a note, or a multiple type such as when using the multiSelectComboBox for a multiple input.

From a Database side I view the data types like array or json/jsonb to be the correct place for this since I can define an enumeration set for the db and allow the array map to contain those elements or just define something like text[] and then parse the array from there in sql. Then there’s just indexing and searching from there, but my understanding of what your describing is that jmix can store these data types but not necessarily read and search for them, so all of my filtering would have to be done entirely on the database side or I would have to make a separate table just for my tags and introduce another join mechanism.

Hopefully I’m just missing something, but that does seem a bit odd to me.

Thank you so much for helping the newbie,
Oran

#6

Jmix relies on JPA for database access. It cannot “just define something like text[] and then parse the array from there in sql”, there is no use of SQL in the framework mechanisms.

To provide high-level features like automatic binding of UI components to data model or components like GenericFilter, the framework has to restrict the variety of underlying data model structures. Now it supports entities mapped to database tables, and relations between them. Database-specific column types can be used to store and load data, but they are largely ignored by the framework features.

So to get the most out of Jmix, it’s best to play by its rules and avoid using database-specific types where possible. This is a common trade-off between advanced features and low-level optimizations.

Regards,
Konstantin

2 Likes