While using Spring Boot with PostgreSQL, have you ever needed to retrieve or persist data to JSONB column? If your answer is yes, and you are visiting this journal entry, chances are high that you need help to solve the error when using JSONB column.

Problem

Imagine you have following sample table.

CREATE TABLE IF NOT EXISTS ichan_schema.sample (
    sample_id TEXT NOT NULL,
    sample_name TEXT NOT NULL,
    child_jsons JSONB DEFAULT '[]'::JSONB NULL,
    CONSTRAINT sample_sample_id_pkey PRIMARY KEY (sample_id)
);

CREATE INDEX IF NOT EXISTS sample_sample_name_idx
    ON ichan_schema.sample (sample_name);

The sample table above have child_jsons column which use JSONB data type. So, how to access child_jsons column from Spring Data JPA?

Solution

There are 2 approaches you might want to use, to access child_jsons column.

Entity

If you are using Hibernate 6—of course with Spring Boot 3—you are in luck, you could simply use @JdbcTypeCode(SqlTypes.JSON) annotation on your field’s entity as following.

@Entity
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Table(name = "sample", schema = "ichan_schema")
public class SampleEntity {

    @Id
    private String sampleId;

    private String sampleName;

    @JdbcTypeCode(SqlTypes.JSON)
    private List<String> childJsons;
}

💡

Accessing child_jsons column using childJsons field is possible, because I set the hibernate.physical_naming_strategy property with org.hibernate.boot.model.naming.CamelCaseToUnderscoresNamingStrategy value. You could check my configuration here.

It’s simple right?

If you want to use the above entity with following repository interface.

@Repository
public interface SampleRepository extends JpaRepository<SampleEntity, String> {
}

You could call the repository save() and findAll() methods to persist and retrieve sample's data as follows.

@Autowired
private final SampleRepository sampleRepository;

public List<SampleEntity> someMethod() {
    // persist
    var sample = SampleEntity
            .builder()
            .sampleId("ID1")
            .sampleName("Sample One")
            .childJsons(List.of("A", "B"))
            .build();
    sampleRepository.save(sample);

    // retrieve
    return sampleRepository.findAll();
}

Interface Based Projection

Ok, maybe you want to use native query with pagination, and it returns SampleDto interface.

@Repository
public interface SampleRepository extends JpaRepository<SampleEntity, String> {

    @Query(value = """
            SELECT sample_id, sample_name, child_jsons
            FROM ichan_schema.sample
            WHERE (:sampleName IS NULL OR sample_name ILIKE :sampleName)
            ORDER BY sample_id ASC
            """, nativeQuery = true)
    Page<SampleDto> getSample(
            @Param("sampleName") String sampleName,
            Pageable pageable
    );
}

Take a look at the native query above, it returns child_jsons column from sample table. With above getSample() repository method, you could write SampleDto interface as follows.

public interface SampleDto {

    @Value("#{target.sample_id}")
    String getSampleId();

    @Value("#{target.sample_name}")
    String getSampleName();

    @Value("#{T(moe.ichan.springdatajpajsonb.util.JsonUtils).parseJsonOrDefault(target.child_jsons, T(java.util.List), null)}")
    List<String> getChildJsons();
}

If you notice the @Value annotation is written using Spring Expression Language (SpEL) to convert the JSONB data type into List<String> data type by calling JsonUtils.parseJsonOrDefault() method, which I prepare as follows.

package moe.ichan.springdatajpajsonb.util;

// some redacted import here
import ...

@UtilityClass
public class JsonUtils {

    private static final ObjectMapper objectMapper = new ObjectMapper();

    public <T> T parseJsonOrDefault(String json, Class<T> clazz, T defaultValue) throws JsonProcessingException {
        if (!StringUtils.hasText(json)) {
            return defaultValue;
        }
        return objectMapper.readValue(json, clazz);
    }
}

Take a look again to the above SampleDto interface snippet. Expression that I write for List getChildJsons(); method is as follows.

#{
    T(moe.ichan.springdatajpajsonb.util.JsonUtils).parseJsonOrDefault(
        target.child_jsons,
        T(java.util.List),
        null
    )
}

I manually indent the expression, to make it easier for you to read. Let me breakdown the expression above:

When SpEL need to call Java’s method, you need to specify complete class path, that’s why I write complete class path to JsonUtils as T(moe.ichan.springdatajpajsonb.util.JsonUtils. You could read this documentation to learn more about Types in SpEL.
parseJsonOrDefault() method have 3 parameters, as follows:
- json parameter, which is passed with target.child_jsons.
- clazz parameter, which is passed with T(java.util.List).
- defaultValue parameter, which is passed with null.

Resources

If you need complete source code of solution, you could check my GitHub repository at ichan-akira/ichan-springdatajpajsonb.

You could also check useful resources I read to help me solve this problem:

Spring Data JPA with PostgreSQL's JSONB

Table of contents