How can solve JSON column in H2

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/

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.

A workaround is actually to create a custom column data type in H2 for the jsonb type, and put the query in the datasource url like this:

spring.datasource.url=jdbc:h2:mem:testdb;INIT=create domain if not exists jsonb as text;MODE=PostgreSQL"

Now for tests and integration tests in particular, it would be preferable to use the same DB than your application, via TestContainers