1. Overview
This tutorial provides a comprehensive understanding of storing JSON data in a PostgreSQL JSONB column.
Using JPA, we’ll quickly review how we can handle a JSON value stored as a variable character (VARCHAR) database column. After that, we’ll compare the differences between the VARCHAR type and the JSONB type to understand the additional features of JSONB. Finally, we’ll address the mapping JSONB type in JPA.
2. VARCHAR Mapping
In this section, we’ll explore converting a JSON value in VARCHAR type to a custom Java POJO. For this purpose, we’ll use*AttributeConverter* to easily convert from an entity attribute value in Java data type to its corresponding value in the database column.
2.1. Maven Dependency
To create an AttributeConverter, we have to include the latest Spring Data JPA dependency in the pom.xml:
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
<version>2.7.18</version>
</dependency>
2.2. Table Definition
Let’s illustrate this concept with a simple example using the following database table definition:
CREATE TABLE student (
student_id VARCHAR(8) PRIMARY KEY,
admit_year VARCHAR(4),
address VARCHAR(500)
);
The student table has three fields, and we’re expecting the address column to store JSON values with the following structure:
{
"postCode": "TW9 2SF",
"city": "London"
}
2.3. Entity Class
To handle this, we’ll create a corresponding POJO class to represent the address data in Java:
public class Address {
private String postCode;
private String city;
// constructor, getters and setters
}
Next, we’ll create an entity class, StudentEntity, and map it to the student table we created earlier:
@Entity
@Table(name = "student")
public class StudentEntity {
@Id
@Column(name = "student_id", length = 8)
private String id;
@Column(name = "admit_year", length = 4)
private String admitYear;
@Convert(converter = AddressAttributeConverter.class)
@Column(name = "address", length = 500)
private Address address;
// constructor, getters and setters
}
We’ll annotate the address field with @Convert and apply AddressAttributeConverter to convert the Address instance into its JSON representation.
2.4. AttributeConverter
Earlier, we mapped the address field in the entity class as a VARCHAR type in the database. However, JPA cannot automatically convert the custom Java type and the VARCHAR type. So, AttributeConverter comes in to bridge this gap by providing a mechanism to handle the conversion process.
We use AttributeConverter to persist a custom Java data type to a database column. It’s mandatory to define two conversion methods for every AttributeConverter implementation. One, named convertToDatabaseColumn(), converts the Java data type to its corresponding database data type, while the other, named convertToEntityAttribute(), converts the database data type to the Java data type:
@Converter
public class AddressAttributeConverter implements AttributeConverter<Address, String> {
private static final ObjectMapper objectMapper = new ObjectMapper();
@Override
public String convertToDatabaseColumn(Address address) {
try {
return objectMapper.writeValueAsString(address);
} catch (JsonProcessingException jpe) {
log.warn("Cannot convert Address into JSON");
return null;
}
}
@Override
public Address convertToEntityAttribute(String value) {
try {
return objectMapper.readValue(value, Address.class);
} catch (JsonProcessingException e) {
log.warn("Cannot convert JSON into Address");
return null;
}
}
}
2.5. Test Case
Now, we can verify that a Student row is persisted correctly along with its Address:
@Test
void whenSaveAnStudentEntityAndFindById_thenTheRecordPresentsInDb() {
String studentId = "23876213";
String postCode = "KT5 8LJ";
Address address = new Address(postCode, "London");
StudentEntity studentEntity = StudentEntity.builder()
.id(studentId)
.admitYear("2023")
.address(address)
.build();
StudentEntity savedStudentEntity = studentRepository.save(studentEntity);
Optional<StudentEntity> studentEntityOptional = studentRepository.findById(studentId);
assertThat(studentEntityOptional.isPresent()).isTrue();
studentEntity = studentEntityOptional.get();
assertThat(studentEntity.getId()).isEqualTo(studentId);
assertThat(studentEntity.getAddress().getPostCode()).isEqualTo(postCode);
}
Moreover, we can check the logs to see what JPA is doing when we’re inserting new data:
Hibernate:
insert
into
"public"
."student_str" ("address", "admit_year", "student_id")
values
(?, ?, ?)
binding parameter [1] as [VARCHAR] - [{"postCode":"KT6 7BB","city":"London"}]
binding parameter [2] as [VARCHAR] - [2023]
binding parameter [3] as [VARCHAR] - [23876371]
We can see that the first parameter has been converted successfully from our Address instance by the AddressAttributeConverter and binds as a VARCHAR type.
We learned how to persist JSON data by converting the JSON data to VARCHAR and vice-versa. Next, let’s check other solutions to handle JSON data.
3. JSONB Over VARCHAR
In PostgreSQL, we can set the type of a column as JSONB to save JSON data:
CREATE TABLE student (
student_id VARCHAR(8) PRIMARY KEY,
admit_year VARCHAR(4),
address jsonb
);
Here, we’re defining the address column as JSONB. This is a different data type than the previously used VARCHAR type, and it is important to learn why we have this data type in PostgreSQL. JSONB is a designated data type for processing JSON data in PostgreSQL.
Moreover**, columns using JSONB type store data in a decomposed binary format, which has a bit of overhead when storing JSON due to the additional conversion**.
Also, JSONB* provides additional features compared to *VARCHAR. Therefore, JSONB is a more favorable choice for storing JSON data in PostgreSQL.
3.1. Validation
The JSONB type enforces data validation to make sure the column value is a valid JSON. So, attempting to insert or update a column of type JSONB with invalid JSON values fails.
To demonstrate this, we can try to insert a SQL query with an invalid JSON value for the address column where, for example, a double quote is missing at the end of the city attribute:
INSERT INTO student(student_id, admit_year, address)
VALUES ('23134572', '2022', '{"postCode": "E4 8ST, "city":"London}');
Running this query in PostgreSQL results throws a validation error indicating the JSON stating that we have an invalid JSON:
SQL Error: ERROR: invalid input syntax for type json
Detail: Token "city" is invalid.
Position: 83
Where: JSON data, line 1: {"postCode": "E4 8ST, "city...
3.2. Querying
PostgreSQL supports querying using JSON columns in SQL queries. JPA supports using native queries to search for records in the database. In Spring Data, we can define a custom query method that finds a list of Student:
@Repository
public interface StudentRepository extends CrudRepository<StudentEntity, String> {
@Query(value = "SELECT * FROM student WHERE address->>'postCode' = :postCode", nativeQuery = true)
List<StudentEntity> findByAddressPostCode(@Param("postCode") String postCode);
}
This query is a native SQL query that selects all Student instances in the database where the address JSON attribute postCode equals the provided parameter.
3.3. Indexing
JSONB supports JSON data indexing. This gives JSONB a significant advantage when we have to query the data by keys or attributes in the JSON column.
Various types of indexes can be applied to a JSON column, including GIN, HASH, and BTREE. GIN is suitable for indexing complex data structures, including arrays and JSON. HASH is important when we only need to consider the equality operator =. BTREE allows efficient queries when we deal with range operators such as <* and *>=.
For example, we could create the following index if we always need to retrieve data according to the postCode attribute in the address column:
CREATE INDEX idx_postcode ON student USING HASH((address->'postCode'));
4. JSONB Mapping
We cannot apply the same AttributeConverter when the databases column is defined as JSONB. Otherwise, our application throws the following error upon start-up if we attempt to:
org.postgresql.util.PSQLException: ERROR: column "address" is of type jsonb but the expression is of type character varying
Even if we change the AttributeConverter class definition to use Object as the converted column value instead of String we’ll still get an error:
@Converter
public class AddressAttributeConverter implements AttributeConverter<Address, Object> {
// 2 conversion methods implementation
}
Our application complains about the unsupported type:
org.postgresql.util.PSQLException: Unsupported Types value: 1,943,105,171
Therefore, we can confidently say that JPA doesn’t support JSONB type natively. However, our underlying JPA implementation, Hibernate, does support JSON custom types that allow us to map a complex type to a Java class.
4.1. Maven Dependency
In short, we need a custom type for JSONB conversion. Fortunately, we can rely on an existing library named Hypersistence Utilities.
Hypersistence Utilities is a general-purpose utility library for Hibernate. One of its features is the definition of JSON column type mapping for different databases such as PostgreSQL and Oracle. Thus, we can include this additional dependency in the pom.xml:
<dependency>
<groupId>io.hypersistence</groupId>
<artifactId>hypersistence-utils-hibernate-55</artifactId>
<version>3.7.0</version>
</dependency>
4.2. Updated Entity Class
Hypersistence Utilities defines different custom types that are database-dependent. In PostgreSQL, we’ll use the JsonBinaryType class for the JSONB column type. In our entity class, we define the custom type using Hibernate’s @TypeDef annotation and then apply the defined type to the address field via @Type:
@Entity
@Table(name = "student")
@TypeDef(name = "jsonb", typeClass = JsonBinaryType.class)
public class StudentEntity {
@Id
@Column(name = "student_id", length = 8)
private String id;
@Column(name = "admit_year", length = 4)
private String admitYear;
@Type(type = "jsonb")
@Column(name = "address", columnDefinition = "jsonb")
private Address address;
// getters and setters
}
For this case of using @Type, we don’t need to apply the AttributeConverter to the address field anymore. The custom type from Hypersistence Utilities handles the conversion task for us, making our code more neat.
Note: Keep in mind that @TypeDef and @Type annotations are deprecated in Hibernate 6.
4.3. Test Case
After all these changes, let’s re-run the Student persistence test case again:
Hibernate:
insert
into
"public"
."student" ("address", "admit_year", "student_id")
values
(?, ?, ?)
binding parameter [1] as [OTHER] - [Address(postCode=KT6 7BB, city=London)]
binding parameter [2] as [VARCHAR] - [2023]
binding parameter [3] as [VARCHAR] - [23876371]
We’ll see that JPA triggers the same insert SQL as before, except the first parameter is binding as OTHER instead of VARCHAR. This indicates that Hibernate binds the parameter as a JSONB type this time.
5. Conclusion
In this tutorial, we learned how to manage JSON data in PostgreSQL using Spring Boot and JPA. First, we addressed the mapping of JSON value to VARCHAR type and JSONB type using a custom converter. Then, we learned about the importance of using JSONB to enforce JSON validation and query and index JSON values easily. Finally, we implemented a custom type conversion of JSONB columns using the Hypersistence library.
As always, the sample code is available over on GitHub.