liquibase exception unknown data type "JSON"

Question

my application use jhipster with mysql and liquibase, and mysql version is 5.7.20 which support json column, and I use ObjectMapper to map json column and java object, and it works, the column type is json, but when the liquibase in involved (./mvnw package -Pprod dockerfile:build), there will be exception like "unknown data type 'JSON'" and the test will be failed, no docker image generated.

I change a bit in 20180410012441_added_entity_Ability.xml for that json column:

     <column name="abilities" type="json">
        <constraints nullable="true"/>
    </column>

in my domain class, the json related field is like:

@Type(type = "json")
@Column(columnDefinition = "json")
private List<Skill> abilities = new ArrayList<>();

I suspect the problem is related for that xml column/type definition, liquibase does not support json keyword, I have no idea what should be there for correct type.

Could someone help please? very appreciated.

========================================================= update 9:01 PM Beijing time I add a changeSet as "

<changeSet id="20180415081741-1" author="jhipster">
  <sql dbms="mysql" endDelimiter="\nGO" splitStatements="true"
     stripComments="true">ALTER TABLE `ability` ADD `abilities` json DEFAULT NULL</sql>
</changeSet>

then I issue "./mvnw package -Pprod dockerfile:build", another exception which seemingly hibernate related:

2018-04-15 20:56:52.186 ERROR 23936 --- [           main] o.h.metamodel.internal.MetadataContext   : HHH015007: Illegal argument on static metamodel field injection : com.james.app.domain.Ability_#abilities; expected type :  org.hibernate.metamodel.internal.SingularAttributeImpl; encountered type : javax.persistence.metamodel.ListAttribute

I'm not sure Liquibase supports this type. Alternatively you could write your changeset as SQL see https://www.liquibase.org/documentation/changes/sql.html although you will still have an issue with unit tests that use H2 which you can work around using conditionals. — Gaël Marziou, Apr 15 '18 at 09:34
Could you please tell which condition I can set? is there some method to check whether mysql is used or not? or just comment out all Ability usages under test? — James Hao, Apr 15 '18 at 12:47
after add dbms="mysql", another exception occurs, please see the updated question content, thanks — James Hao, Apr 15 '18 at 13:02
Well this is related to JPA filtering. You could disable it for this entity as I don't know if it's really useful in your case. — Gaël Marziou, Apr 15 '18 at 13:11
seems a hibernate bug, https://hibernate.atlassian.net/browse/HHH-12338, but the fixed version is not available yet. — James Hao, Apr 15 '18 at 14:12

Hamza Assada · Answer 1 · 2021-10-27T02:38:56.523

For those who still have this problem in either H2 and PostgreSQL database even after defining a TypeDef ...etc, after doing the following:

@Entity
@TypeDef(name = "jsonb", typeClass = com.vladmihalcea.hibernate.type.json.JsonBinaryType.class)
class SomeEntity {
   // ...
   @Type(type = "jsonb")
   @Column(columnDefinition = "jsonb")
   private List<Skill> abilities = new ArrayList<>();
   // ...
}

You need to register the type by extending the Database dialect and using that extended dialect as the dialect instead of the original dialect:

1st define the new extended dialect:

import org.hibernate.dialect.PostgreSQL10Dialect;
import javax.inject.Inject;

public class AMPostgresDialect
    extends PostgreSQL10Dialect
{
    @Inject
    public AMPostgresDialect()
    {
        super();
        registerColumnType( Types.JAVA_OBJECT, "jsonb" );
    }
}

and if you have an H2 Database for the testing profile:

import org.hibernate.dialect.H2Dialect;
import java.sql.Types;

public class DhisH2Dialect extends H2Dialect
{
    public DhisH2Dialect()
    {
        super();
        registerColumnType( Types.JAVA_OBJECT, "text" );
        registerColumnType( Types.JAVA_OBJECT, "json" );
    }

and then in the application-*.yml properties file you use those extended dialects instead of the original one.

score 1 · Answer 2 · edited Feb 03 '20 at 07:59

1

Change the liquibase column type as jsonb and try.

<column name="abilities" type="jsonb">
        <constraints nullable="true"/>
</column>

Add the type and column definition as jsonb.

@Type(type = "jsonb")
@Column(columnDefinition = "jsonb")
private List<Skill> abilities = new ArrayList<>();

edited Feb 03 '20 at 07:59

TAbdiukov

1,185
3
12
25

answered Feb 03 '20 at 05:26

Pavan Kumar Reddy

11
1

Not working, it throw an `liquibase.exception.UnexpectedLiquibaseException: loadData type of 'jsonb' ' is not supported. Please use BOOLEAN, NUMERIC, DATE, STRING, COMPUTED, SEQUENCE, UUID or SKIP` – 2MuchSmoke Jan 24 '21 at 06:02

score 0 · Answer 3 · answered Mar 12 '21 at 11:46

Considering you are using Oracle, Check this https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adjsn/creating-a-table-with-a-json-column.html#GUID-E6CC0DCF-3D72-41EF-ACA4-B3BF54EE3CA0 for creating table with JSON column. Use the command in <sql> </sql> in liquibase.

liquibase exception unknown data type "JSON"

3 Answers3

Linked