# Spring Data JPA with PostgreSQL's JSONB

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.

```sql
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.

```java
@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;
}
```

<div data-node-type="callout">
<div data-node-type="callout-emoji">💡</div>
<div data-node-type="callout-text">Accessing <code>child_jsons</code> column using <code>childJsons</code> field is possible, because I set the <code>hibernate.physical_naming_strategy</code> property with <code>org.hibernate.boot.model.naming.CamelCaseToUnderscoresNamingStrategy</code> value. You could check <a target="_blank" rel="noopener noreferrer nofollow" href="https://github.com/ichan-akira/ichan-springdatajpajsonb/blob/main/src/main/java/moe/ichan/springdatajpajsonb/config/PostgresqlConfiguration.java#L45" style="pointer-events: none">my configuration here</a>.</div>
</div>

It’s simple right?

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

```java
@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.

```java
@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.

```java
@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.

```java
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.

```java
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.

```plaintext
#{
    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](https://docs.spring.io/spring-framework/reference/core/expressions/language-ref/types.html) 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](https://github.com/ichan-akira/ichan-springdatajpajsonb).

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

* [Spring Data JPA: Projections](https://docs.spring.io/spring-data/jpa/reference/repositories/projections.html)
    
* [Invoke method using spring SPEL with property](https://stackoverflow.com/a/72046772/5948846)
    
* [Calling static methods from Spring Security Expressions?](https://stackoverflow.com/a/7585812/5948846)