Support jakarta.json.JsonValue as a persistence capable type

[jgrassel]

We should consider adding jakarta.json.JsonValue as a persistent capable type. As JSON is nothing more than formatted text, it easily maps to VARCHAR. This reduces the necessity for application developers to implement a converter to do this bit of work.

Example Usecase (borrowed from eclipse-ee4j/eclipselink#1391):

@Entity
public class JsonEntity {
    @Id
    private long id;

    private JsonValue value;

    public JsonEntity() {
        this.id = 0;
        this.value = null;
    }

    public JsonEntity(final long id, final JsonValue value) {
        this.id = id;
        this.value = value;
    }

    public long getId() {
        return id;
    }

    public void setId(final long id) {
        this.id = id;
    }

    public JsonValue getValue() {
        return value;
    }

    public void setValue(final JsonValue value) {
        this.value = value;
    }
}

@Test
public void testCreateJsonField() {
    EntityManager em = emf.createEntityManager();

    JsonValue value = Json.createObjectBuilder()
            .add("id", "1001")
            .add("name", "Joe Wright")
            .add("age", 49)
            .build();

    try {
        em.getTransaction().begin();
        JsonEntity e = new JsonEntity(1001, value);
        em.persist(e);
        em.getTransaction().commit();

        JsonEntity dbValue = em.createQuery("SELECT v FROM JsonEntity v WHERE v.id=:id", JsonEntity.class)
                .setParameter("id", e.getId()).getSingleResult();
        Assert.assertEquals(value, dbValue.getValue());

    } finally {
        if (em.getTransaction().isActive()) {
            em.getTransaction().rollback();
        }
        em.close();
    }
}

[lukasj]

note it should be about jakarta.json.JsonValue (not javax)

[jgrassel]

Good catch! Fixed!

[lukasj]

@gavinking do you think this is something we can find agreement on among vendors within remaining timeframe for 3.1 (say 3-4w), should we try and see or should we rather postpone it to the next release right away?

[lukasj]

Mapping to VARCHAR should probably be fallback if the DB vendor does not support Json through its own datatype

[gavinking]

Ummmmm .... not sure because I've never used JSON-P or JsonValue and I don't know enough to really have an opinion. My quick, very naive reaction:

• If this were limited to mapping it to varchar, then I don't see why it would be hard to add. (Of course the natural mapping on some databases is to a native JSON type, but that's OK, a similar comment applies to UUID.)
• After a very quick search I didn't turn up any examples of people asking for this on stackoverflow. For whatever that's worth.
• My initial reaction is that this has surely got to be very easy to handle via a converter, and I don't naively see a lot of pressure to have it as a built-in type. (Except, perhaps, to have it automatically map to the database-native JSON type, but if that bit is outta scope then.....)
• The existing JPA built-in types are all types that are provided by the JDK itself. This would be the first one that comes from a library.

So I dunno, I mean, it's doable, but it doesn't seem critical. If we're going to start adding new built-in types I'm not clear that this would be on the top of the list.

Of course I might be missing some extra good reason to do it beyond "save users from having to write a converter".

The situation with UUID was a bit different because it's a commonly-used identifier type, and comes with the JDK.

But again, I'm not sure.

[lukasj]

the use-case for this came from cloud native requirements (whatever that means). The idea behind is to simplify the scenario where a REST endpoint wants to persist data, or part of it, it receives to the DB. In most cases, I believe, data are sent in json format, so this can help avoiding the need to implement unnecessary converter or logic in the REST MessageReader/Writer for getting data from the endpoint through the persistence entity to the DB. JSON format as such is also quite popular and with DBs having native support for JSON data type, it may make sense to support it at the spec level.

from the other point of view - simplicity of handling this via converter and the fact that JsonValue is not built-in type in JDK - are valid concerns and points which were raised during the conversation I've had with Linda before EE 8 (many years ago). At that time, there was an intent to include jsonp in the JDK, so the second concern would become invalid, but that actually has never happened and is not going to happen (at least not anytime in the foreseeable future).

[beikov]

FWIW in Hibernate 6 we have the option to annotate a SqlTypes type code, which is a superset of java.sql.Types per attribute which we use to model what the intended JDBC type should be. So with Hibernate 6 one could do

@JdbcTypeCode(SqlTypes.JSON)
@Column
String jsonPayload;

and if the underlying database supports a JSON type, it would use that DDL type, otherwise use some VARCHAR and interact through JDBC however that works for the respective JDBC driver and database.

We don't yet support JSONP types, but I is that really the natural representation one would use in an entity? I would actually expect that one either uses a proper model type MyJsonPayloadRepresentation or simply a String.

If so that is the case, then maybe we just need to agree on such a SqlTypes class representing a superset of java.sql.Types as well as an annotation to declare the sql/jdbc type for attributes within the JPA spec?

[rbygrave]

FWIW With Ebean we have @DbJson such that we can specify:

• JSON or JSONB database type to use when available
• The fallback type for databases with non-native JSON support e.g. varchar(2000)
• The mutation detection mechanism to use for the column/content to supports change logs etc (None, Source, Hash, Default).

support JSONP types, but I is that really the natural representation one would use in an entity?

For myself, I personally feel it is not ideal to map to JsonValue but instead use a specific application dto type like MyJsonPayloadRepresentation or JDK type like Map<String, Object>. This then keeps the model independent of the specific JSON binding library.

Note that Ebean supports mapping JSON content to Jackson's JsonNode type but personally I now believe that was a mistake. People using Ebean generally map JSON content to their own types like MyJsonPayloadRepresentation or String but they can also otherwise map to JDK types like Map<String,Object> or even simple arrays/objects like List<String>, List<Long>, Map<String,String> when the content is known to match those types.

Given those options for mapping JSON to types that keep the model independent of the applications JSON binding library should people use a Jakarta JsonValue type?

[Tomas-Kraus]

I did some trials and errors with EclipseLink and here are my findings:

Mapping and Conversion:

• Default: jakarta.json.JsonValue <-> VARCHAR -- works fine
• MySQL: jakarta.json.JsonValue <-> java.lang.String <-> JSON -- works fine
• PostgreSQL: jakarta.json.JsonValue <-> java.lang.String <-> JSON -- works fine
• Oracle 21c: jakarta.json.JsonValue <-> java.lang.String <-> JSON -- works fine

There won't be problem to store and retrieve JSON data. Adding it to mapping just removed the need of any annotations on Entity attribute so it may help API users a bit.

JSON in WHERE conditions:
This is where all the fun begins. I did some tests with VARCHAR and native JSON types using whole JsonObject.
Simple tests passed on Default (VARCHAR), and PostgreSQL/Oracle 21c JSON data types. MySQL did not return any matching row.
Also VARCHAR usage has some limitations when JSON serialization produces different output, e.g.

{"id":1234,"value":"Some Value"}

vs.

{"value":"Some Value","id":1234}

-> Imagine you have 1st one in DB and second comes to some .setParameter("value", value) of the query statement. VARCHAR condition won't work properly.

Another story is to introduce JSON path in JPQL and Criteria conditions. That would enhance JSON support a lot, but it won't work without native support on DB side. Using VARCHAR for such a thing is not possible. Also every DB would require it's own native SQL syntax for such a thing (look at PostgreSQL vs MySQL as an example).

My conclusion is, that we can add mapping for jakarta.json.JsonValue to the spec. But there will be some limitations now. People shall consider it as some kind of CLOB/TEXT without using it in search conditions except DBs where native JSON type works fine.

Edit:

• Fixed PLSQL -> JPQL
• MySQL issue resolved too, it just requires CAST to JSON in condition expressions.

[Tomas-Kraus]

For myself, I personally feel it is not ideal to map to JsonValue but instead use a specific application dto type like MyJsonPayloadRepresentation or JDK type like Map<String, Object>. This then keeps the model independent of the specific JSON binding library.

This is EE4J JPA API specification and jakarta.json.JsonValue is common ancestor of all JSON values in EE4J JSON API specification. That's why JsonValue should be our primary choice - to keep consistency of the whole EE4J spec/API bundle.

[rbygrave]

This is EE4J JPA API specification

Yup, cool.

jakarta.json.JsonValue ... in EE4J JSON API

I was just trying to say that in my experience it has been less useful in practical terms to map a type like JsonValue than it maybe first seems. Less useful than mapping MyJsonPayloadRepresentation or List<String> etc. I presume you have a customer expressing a need for mapping this type which is fine.

introduce JSON path in PLSQL

I am thinking you meant introduce JSON path in JPQL right?

JSON in WHERE conditions / Also every DB would require it's own native SQL syntax for such a thing

There is a SQL/JSON standard for DB JSON path functions and expressions and improving support for that standard amongst the databases. So optimistically there is becoming less need for DB specific syntax but today internally ORM's need to "translate" those expressions to DB specific syntax in some cases.

JSON serialization produces different output

As far as I know this is MySql specific behavior and I don't recall hitting this behavior with any other database. As I see it, this can be problematic that the content coming back is somewhat unpredictable (e.g. an app wanting to do MD5 on the json content for diff change/detection etc).

Mapping and Conversion:

Just noting that wrt Postgres, I suspect there is a school of thought that JSONB would be the expected default database type to use rather than JSON. For example, CockroachDB only maps/supports JSONB.

[gavinking]

For myself, I personally feel it is not ideal to map to JsonValue but instead use a specific application dto type like MyJsonPayloadRepresentation or JDK type like Map<String, Object>. This then keeps the model independent of the specific JSON binding library.

I'm inclined to agree with this. As a general principle, I prefer to keep entity classes free of dependencies to these sort of "technical" library-defined types. Now, I don't think one needs to be religious about that, but as a rule of thumb it's pretty good.

[Tomas-Kraus]

JSON serialization produces different output

As far as I know this is MySql specific behavior and I don't recall hitting this behavior with any other database. As I see it, this can be problematic that the content coming back is somewhat unpredictable (e.g. an app wanting to do MD5 on the json content for diff change/detection etc).

MySQL problem was just my stupidity. CAST(? AS JSON) does the trick.
Think most of the JSON specific types in relational DBs seem to work fine. At least those I was testing.
The only problem is default usage of VARCHAR where serialization will cause problems with content matching. Especially when producer and consumer of the data are not the same - 1st can be our EE4J, but 2nd can be written in different language with it's own json string-writter rules.

Postgres JSONB vs. JSON

I do agree that JSONB is better. Already used it in EclipseLink schema framework for this platform.
Anyway, I would like to have JPA spec not exactly telling what DB type shall be used. Some DBs have JSON, some JSONB and some both of them. :)

JsonValue vs. Map<String, Object> or MyJsonPayloadRepresentation

Keep in mind, that JSON is document format and it's structure is a tree. Map or some POJO or any other kind of Java Collection is just flat structure that allows to store just one level of the tree.
So JsonValue can be transformed to Map/List structure (JsonObject is Map and JsonArray is List too). But you may need your own code in JPA implementation to traverse and serialize/read such a structure.

There are use cases where flat structure is fine and maybe it makes sense to allow mapping of Java Map/List/POJO to DB JSON too.
On the other hand JsonValue adds an option to store document of any depth.

[rbygrave]

Map or some POJO or any other kind of Java Collection is just flat structure that allows to store just one level of the tree.

No in that Map<String,Object> can store nested tree structure and so can say List<Object>. The Object can itself be a Map or List or scalar json type etc.

JsonValue adds an option to store document of any depth.

Noting that Map<String,Object> also can also store a document of any depth.