Postgres jsonb to Jackson JsonNode With Spring Data JPA

This time, I would like to show you how to solve a pretty common issue when mapping Postgres jsonb to Jackson JsonNode in Spring Data JPA.
The image is a featured image for post about Postgres jsonb and JsonNode mapping and contains Spring Boot logo in the foreground and two monitors standing on a desk in the blurred background.

1. Introduction

Hello friend! In this post, I would like to show you how to solve a pretty common issue when mapping Postgres jsonb to Jackson JsonNode in Spring Data JPA.

If you are not a regular reader of my blog, then I’m pretty sure that you wanted to map a PostgreSQL jsonb field in your Spring Boot with Spring Data JPA application to Jsonnode and you saw something like that:

org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘entityManagerFactory’ defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaConfiguration.class]: Could not determine recommended JdbcType for `com.fasterxml.jackson.databind.JsonNode`

If that’s the case, then you’ve come to the right place. 😁

2. Quick Setup

Before we learn how to solve the issue with Postgres jsonb mapping to JsonNode, let’s take a minute to reproduce the error.

Note: If you would like to cut straight to the point and see how to implement a mapper, then you can skip this part and navigate directly to chapter 3.

2.1. PostgreSQL Table With jsonb column

Firstly, we need to have a PostgreSQL table with jsonb column:

Image shows a diagram of PostgreSQL table called some_table with columns definitions (including a jsonb column), which will be used to demonstrate how to map Postgres jsonb to JsonNode.
Diagram generated with https://dbdiagram.io/

As we can see, it’s just a dummy table called some_table, which contains 4 example columns.

And to be on the same it page, I created it and populated with the following SQL script:

create table some_table(
  id serial not null primary key,
  string_column text not null,
  integer_column smallint not null,
  jsonb_column jsonb not null
);

insert into 
  some_table(string_column, integer_column, jsonb_column)
values
  ('value-1', 1, '{ "some_field": "some_value"}'),
  ('value-2', 2, '{ "another_field": "some_value_2"}');

Make a real progress thanks to practical examples, exercises, and quizzes.

Image presents a Kotlin Course box mockup for "Kotlin Handbook. Learn Through Practice"

2.2. Spring Boot and Dependencies

Nextly, let’s create an example Spring Boot project. As always, we can do that with the Spring Initializr page.

Please configure it according to your needs, but do not forget to add the PostgreSQL Driver and Spring Data JPA dependencies. Additionally, we will need to enrich the project with Jackson Databind:

implementation("org.springframework.boot:spring-boot-starter-data-jpa")
implementation("com.fasterxml.jackson.core:jackson-databind:2.14.1")
runtimeOnly("org.postgresql:postgresql")

2.3. application.yaml

With that being done, let’s specify the connection details:

spring:
  datasource:
    url: "jdbc:postgresql://localhost:5432/postgres"
      username: postgres
      password: postgres

2.4. Implement Entity

As the next step, let’s open up the project and add the SomeEntity data class:

@Entity
@Table(name = "some_table")
data class SomeEntity(
  @Id
  val id: Long? = null,
  val stringColumn: String,
  val integerColumn: Int,
  val jsonbColumn: JsonNode
)

As we can see, we use the @Entity annotation to mark the class as the entity and @Table to explicitly provide Spring with the table name. Moreover, the necessary @Id annotation specifies the primary key of our entity and the jsonbColumn will contain the value of the Postgres jsonb column mapped to JsonNode.

2.5. Create JPA repository

With that being done, let’s add the SomeEntityRepository interface:

interface SomeEntityRepository : CrudRepository<SomeEntity, Long>

This time, the only thing we have to do is to extend the CrudRepository interface, which comes with a basic CRUD operations.

2.6. Create JPA repository

Lastly, let’s add the logic, which will be run when the application starts:

@Component
class MyRunner(
  private val repository: SomeEntityRepository
) : CommandLineRunner {

  override fun run(vararg args: String?) {
    repository.findAll()
      .forEach(::println)
  }
}

As we can see, a repository instance will be injected into this simple component. Moreover, we implement the run method of CommandLineRunner, which instructs Spring Boot to run after the app starts (if you would like to learn more about it, then check out my article about the Spring Boot console app).

Finally, we want to print out all entities, nevertheless, such a stack trace appears to our eyes:

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaConfiguration.class]: Could not determine recommended JdbcType for `com.fasterxml.jackson.databind.JsonNode`
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1751) ~[spring-beans-6.0.2.jar:6.0.2]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:599) ~[spring-beans-6.0.2.jar:6.0.2]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:521) ~[spring-beans-6.0.2.jar:6.0.2]
at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:326) ~[spring-beans-6.0.2.jar:6.0.2]
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234) ~[spring-beans-6.0.2.jar:6.0.2]
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:324) ~[spring-beans-6.0.2.jar:6.0.2]
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:200) ~[spring-beans-6.0.2.jar:6.0.2]
at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1130) ~[spring-context-6.0.2.jar:6.0.2]
at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:905) ~[spring-context-6.0.2.jar:6.0.2]
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:584) ~[spring-context-6.0.2.jar:6.0.2]
at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:730) ~[spring-boot-3.0.0.jar:3.0.0]
at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:432) ~[spring-boot-3.0.0.jar:3.0.0]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:308) ~[spring-boot-3.0.0.jar:3.0.0]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1302) ~[spring-boot-3.0.0.jar:3.0.0]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1291) ~[spring-boot-3.0.0.jar:3.0.0]
at com.codersee.jsonbtojsonnode.JsonbToJsonNodeApplicationKt.main(JsonbToJsonNodeApplication.kt:14) ~[main/:na]
Caused by: org.hibernate.type.descriptor.java.spi.JdbcTypeRecommendationException: Could not determine recommended JdbcType for `com.fasterxml.jackson.databind.JsonNode`
at org.hibernate.type.descriptor.java.spi.UnknownBasicJavaType.getRecommendedJdbcType(UnknownBasicJavaType.java:37) ~[hibernate-core-6.1.5.Final.jar:6.1.5.Final]
at org.hibernate.boot.model.process.internal.InferredBasicValueResolver.from(InferredBasicValueResolver.java:222) ~[hibernate-core-6.1.5.Final.jar:6.1.5.Final]
at org.hibernate.mapping.BasicValue.buildResolution(BasicValue.java:507) ~[hibernate-core-6.1.5.Final.jar:6.1.5.Final]
at org.hibernate.mapping.BasicValue.resolve(BasicValue.java:315) ~[hibernate-core-6.1.5.Final.jar:6.1.5.Final]
at org.hibernate.mapping.BasicValue.resolve(BasicValue.java:305) ~[hibernate-core-6.1.5.Final.jar:6.1.5.Final]
at org.hibernate.boot.internal.InFlightMetadataCollectorImpl.lambda$processValueResolvers$4(InFlightMetadataCollectorImpl.java:1767) ~[hibernate-core-6.1.5.Final.jar:6.1.5.Final]
at java.base/java.util.ArrayList.removeIf(ArrayList.java:1682) ~[na:na]
at java.base/java.util.ArrayList.removeIf(ArrayList.java:1660) ~[na:na]
at org.hibernate.boot.internal.InFlightMetadataCollectorImpl.processValueResolvers(InFlightMetadataCollectorImpl.java:1766) ~[hibernate-core-6.1.5.Final.jar:6.1.5.Final]
at org.hibernate.boot.internal.InFlightMetadataCollectorImpl.processSecondPasses(InFlightMetadataCollectorImpl.java:1752) ~[hibernate-core-6.1.5.Final.jar:6.1.5.Final]
at org.hibernate.boot.model.process.spi.MetadataBuildingProcess.complete(MetadataBuildingProcess.java:300) ~[hibernate-core-6.1.5.Final.jar:6.1.5.Final]
at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.metadata(EntityManagerFactoryBuilderImpl.java:1350) ~[hibernate-core-6.1.5.Final.jar:6.1.5.Final]
at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:1421) ~[hibernate-core-6.1.5.Final.jar:6.1.5.Final]
at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:66) ~[spring-orm-6.0.2.jar:6.0.2]
at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:376) ~[spring-orm-6.0.2.jar:6.0.2]
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:409) ~[spring-orm-6.0.2.jar:6.0.2]
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:396) ~[spring-orm-6.0.2.jar:6.0.2]
at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.afterPropertiesSet(LocalContainerEntityManagerFactoryBean.java:352) ~[spring-orm-6.0.2.jar:6.0.2]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1797) ~[spring-beans-6.0.2.jar:6.0.2]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1747) ~[spring-beans-6.0.2.jar:6.0.2]
... 15 common frames omitted

3. Map Postgres jsonb to JsonNode

So at this point, we can see that Spring “Could not determine recommended JdbcType for `com.fasterxml.jackson.databind.JsonNode`“.

Image shows two ebooks people can get for free after joining newsletter

One of the possible solutions is to implement a custom AttributeConverter and mark it with @Converter annotation:

@Converter(autoApply = true)
class MyConverter : AttributeConverter<JsonNode, String> {

  private val mapper = ObjectMapper()

  override fun convertToDatabaseColumn(node: JsonNode): String {
    return node.asText()
  }

  override fun convertToEntityAttribute(data: String): JsonNode {
    return mapper.readTree(data)
  }
}

As we can see, the AttributeConverter interface comes with two methods: convertToDatabaseColumn and convertToEntityAttribute, which will be invoked when mapping between the entity and the database.

Additionally, we have set the autoApply to true (the default is false) to inform the persistence provider that it should use this converter to map between Postgres jsonb and JsonNode. Without it, we would have to explicitly annotate the desired field:

@Convert(converter = MyConverter::class)
val jsonbColumn: JsonNode

Of course, the choice is up to you and may vary depending on your project, but most of the time you would like to choose the first solution.

Finally, if we run the application, we should see that everything is working, as expected and logs contain the following lines:

SomeEntity(id=1, stringColumn=value-1, integerColumn=1, jsonbColumn={"some_field":"some_value"})
SomeEntity(id=2, stringColumn=value-2, integerColumn=2, jsonbColumn={"another_field":"some_value_2"})

4. Summary

And that’s all for this short, hands-on article on how to map Postgres jsonb to JsonNode. As always, you can find the source code in this GitHub repository.

As always, happy to hear your thoughts on how would you solve this issue.

Share this:

Related content

Newsletter
Image presents 3 ebooks with Java, Spring and Kotlin interview questions.

Never miss any important updates from the Kotlin world and get 3 ebooks!

You may opt out any time. Terms of Use and Privacy Policy