How can solve JSON column in H2

org.h2.jdbc.jdbcsqlexception: unknown data type: "jsonb";
h2 database
h2 timestamp
h2 rename column
hibernate json column
h2 postgres mode
h2 database default value
h2 with clause

I use in application MySQL 5.7 and I have JSON columns. When I try running my integration tests don't work because the H2 database can't create the table. This is the error:

2016-09-21 16:35:29.729 ERROR 10981 --- [           main] org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000389: Unsuccessful: create table payment_transaction (id bigint generated by default as identity, creation_date timestamp not null, payload json, period integer, public_id varchar(255) not null, state varchar(255) not null, subscription_id_zuora varchar(255), type varchar(255) not null, user_id bigint not null, primary key (id))
2016-09-21 16:35:29.730 ERROR 10981 --- [           main] org.hibernate.tool.hbm2ddl.SchemaExport  : Unknown data type: "JSON"; SQL statement:

This is the entity class.

@Table(name = "payment_transaction")
public class PaymentTransaction extends DomainObject implements Serializable {

    @Convert(converter = JpaPayloadConverter.class)
    @Column(name = "payload", insertable = true, updatable = true, nullable = true, columnDefinition = "json")
    private Payload payload;

    public Payload getPayload() {
        return payload;
    }

    public void setPayload(Payload payload) {
        this.payload = payload;
    }
}

And the subclass:

public class Payload implements Serializable {

    private Long userId;
    private SubscriptionType type;
    private String paymentId;
    private List<String> ratePlanId;
    private Integer period;

    public Long getUserId() {
        return userId;
    }

    public void setUserId(Long userId) {
        this.userId = userId;
    }

    public SubscriptionType getType() {
        return type;
    }

    public void setType(SubscriptionType type) {
        this.type = type;
    }

    public String getPaymentId() {
        return paymentId;
    }

    public void setPaymentId(String paymentId) {
        this.paymentId = paymentId;
    }

    public List<String> getRatePlanId() {
        return ratePlanId;
    }

    public void setRatePlanId(List<String> ratePlanId) {
        this.ratePlanId = ratePlanId;
    }

    public Integer getPeriod() {
        return period;
    }

    public void setPeriod(Integer period) {
        this.period = period;
    }

}

And this converter for insert in database:

public class JpaPayloadConverter implements AttributeConverter<Payload, String> {

    // ObjectMapper is thread safe
    private final static ObjectMapper objectMapper = new ObjectMapper();

    private Logger log = LoggerFactory.getLogger(getClass());

    @Override
    public String convertToDatabaseColumn(Payload attribute) {
        String jsonString = "";
        try {
            log.debug("Start convertToDatabaseColumn");

            // convert list of POJO to json
            jsonString = objectMapper.writeValueAsString(attribute);
            log.debug("convertToDatabaseColumn" + jsonString);

        } catch (JsonProcessingException ex) {
            log.error(ex.getMessage());
        }
        return jsonString;
    }

    @Override
    public Payload convertToEntityAttribute(String dbData) {

        Payload payload = new Payload();
        try {
            log.debug("Start convertToEntityAttribute");

            // convert json to list of POJO
            payload = objectMapper.readValue(dbData, Payload.class);
            log.debug("JsonDocumentsConverter.convertToDatabaseColumn" + payload);

        } catch (IOException ex) {
            log.error(ex.getMessage());
        }
        return payload;

    }
}

JSON support was added to H2 after the question was asked, with version 1.4.200 (2019-10-14).

However, you rarely need a JSON data type in a database. JSON essentially is just a potentially very long string, so you can use CLOB which is available on most databases.

You do need the JSON data type if you need an SQL function that operates on them, and then only if the database insists that its JSON functions operate on a JSON type instead of on a CLOB. Such functions tend to be database-dependent though.

Support for JSON data type · Issue #1869 · h2database/h2database , We have a lot of things to do before a new release, so do not expect a release in the near future, it is very unlikely. You can buld H2 from the  One solution is to use FORMAT JSON when inserting the json, but then you need to have duplicate insert java spring hibernate h2 mysql-5.7 answered Mar 23 at 20:36

I just came across this problem working with the JSONB column type - the binary version of the JSON type, which doesn't map to TEXT.

For future reference, you can define a custom type in H2 using CREATE DOMAIN, as follows:

CREATE domain IF NOT EXISTS jsonb AS other;

This seemed to work for me, and allowed me to successfully test my code against the entity.

Source: https://objectpartners.com/2015/05/26/grails-postgresql-9-4-and-jsonb/

jsonb work around for H2 database · GitHub, Hi,. But without columnDefinition="jsonb" you can't create column in table if it doesn't exists. How do you solve it? Luckily, you can do that already by using user defined functions. Similar to the XML functions in H2, that can be ported to other databases. I don't think supporting a JSON data type is all that important (similar to an XML data type). Just use VARCHAR or CLOB.

I have solved the problem using TEXT type in H2. One must create a separate database script to create schema in H2 for tests and replace the JSON type by TEXT.

It is still a problem since if you use Json function in queries, you will not be able to test those while with H2.

How can solve JSON column in H2, I use in application MySQL 5.7 and I have JSON columns. When I try running my integration tests don't work because the H2 database can't create the table. Solve common issues with JSON in SQL Server. If you want to return an object or an array as a column, use the AS JSON option in the column definition, as shown in

In my case we were dealing with PostgreSQL jsonb type in production and H2 for our tests.

I could not test @n00dle 's solution because apparently spring does not support executing a SQL script before Hibernate's ddl-auto=update for our tests so I used another way to solve this.

Here is a gist for it.

The overall idea is to create two package-info files. One for production and the other for tests and register different types (JsonBinaryType.class for production and TextType.class for tests) to handle them differently for PostgreSQL and H2

Data Types, I use in application MySQL 5.7 and I have JSON columns. When I try running my integration tests don't work because the H2 database can't create the table. JSON Path Expression for tExtractJSONFields and tFileInputJSON Talend components I see, one of the main reason why Talend Open Studio (TOS) become a famous ETL tool because it support JSON and XML data handling in very convenient way whereas SSIS is not support or need custom codes to handle it.

H2 does not have the JSON data type.

In MySQL the JSON type is just an alias for the LONGTEXT data type so the actual data type for the column will be LONGTEXT.

How can solve JSON column in H2, I notice that JSON support is way down on priority 2 list. If you use a relational database, I assume you want to use a solution that works not a few JSON type columns to be properly interpreted by H2 (for unit test purposes)  tWriteJSONField output a new flux with the same columns as the input (although, this columns are all empty in the output even if they were populated in input) and add a new one jsonStructure which contains generated JSON. This new flux is caught by a tExtractJSONFields (configuration for this component is available in my original post).

JSON support, Learn how to map JSON column types to JPA entity attributes using the The first thing you need to do is to set up the following Maven  See also timestamp with time zone literal grammar. Mapped to org.h2.api.TimestampWithTimeZone by default and can be optionally mapped to java.time.OffsetDateTime. java.time.ZonedDateTime and java.time.Instant are also supported on Java 8 and later versions. Values of this data type are compared by UTC values.

How to map JSON objects using generic Hibernate Types, Daniel. I agree, it would be useful for a project I'm working on now for our flyway database scripts for MySQL that have a few JSON type columns to be properly  Optional window order clause specifies order of rows in the partition. If some rows have the same order position they are considered as a group of rows in optional window frame clause. Optional window frame clause specifies which rows are processed by a window function, see its documentation for a more details.

[h2] JSON support, Currently I am facing an issue with table generation in H2 database using liquibase. class where we register the jsonb datatype. following is the example of custom dialect class. registerColumnType(Types. Please help in solving this issue I need to run H2 for my unit test cases but for integration test  -- IN H2 database, create a column as 'OTHER' data type, -- if H2 fails to create a column as 'JSON' data type. CREATE DOMAIN IF NOT EXISTS JSON AS OTHER; This .sql file will be executed during startup of your application and will create a column with data type others in table for the 'json' data type columns in H2 database.

Comments
  • did overcome the problem ? im also trying to do something similar and dont succeed
  • update: as of 1.4.200, H2 does have a native JSON type: h2database.com/html/changelog.html
  • Updated the answer.
  • Hi, could I ask you more about this? I have the exact same issue I believe, but when I put that line before the place where I create the table, I get an error type not found or user lacks privilege: NOT
  • I'm afraid I'm no longer working on this sort of thing. I've not seen that error before though.
  • Unfortunatly, I'll have to use somethings else as I'm using the Spring @Type annotation which fails if I change JSON type to TEXT or CLOB into liquibase changelog file.
  • I would suggest that you deactivate the hibernate table creation for your test if you are using a different database type like H2. Use a spring profile to deactivate hibernate auto create and use a liquibase profile to use a different script for your test environnement.
  • You could also use Spring configurations with different profile to manage test entities vs prod entities and still using @Type and hibernate auto create. You then have to create "test" entities in a different package. in my opinion, it's a bad design and it's probably overkill and I would personnally deactivate autocreate and use custom liquibase scripts with different liquibase profiles. Hibernate table management is, again in my own opinion and experiences, too rigid to be used with multiple database.
  • Thanks for your replies :) I managed to replace H2 by EmbeddedPostgreSQL for running tests. It's a bit slower to startup, but it will prevent myself from future issues. Liquibase is not 100% DB agnostic, they are doing a wonderful job, but I had few issues when trying to use Oracle/Postgres switch and Postgres/H2.