Storing PostgreSQL JSONB Using Spring Boot and JPA

PostgreSQL’s JSONB data type provides a way to store and query JSON (JavaScript Object Notation) data in a structured format. Unlike JSON, JSONB stores the data in a binary format, enabling efficient access, indexing, and querying. This makes it ideal for handling semi-structured data in a relational database. In Spring Boot applications, JPA (Java Persistence API) makes it easy to map and work with JSONB data types, enabling seamless integration between Java code and the database.

This article will demonstrate how to configure a Spring Boot application to store, retrieve, and query JSONB data using JPA.

Prerequisites:

• Basic knowledge of the Spring Boot and JPA.
• PostgreSQL installed and running.
• PostgreSQL driver dependency added in the pom.xml.
• Maven for building dependency management.

Storing JSONB Data in PostgreSQL Using Spring Boot and JPA

The main concept revolves around storing, retrieving, and querying semi-structured data (JSON) in a relational database (PostgreSQL) using the JSONB data type, and how Spring Boot and JPA make this seamless for developers.

What is JSONB?

PostgreSQL provides two JSON data types:

• JSON: This type stores the JSON data in text format. While this allows easy storage, operations like indexing and querying become slower as the JSON needs to be parsed every time.
• JSONB: This stands for JSON Binary. It stores JSON data in a decomposed binary format, making it more efficient for querying, indexing, and updating.

The advantages of using JSONB include:

• Efficient indexing: PostgreSQL allows you to create indexes on JSONB fields, making it faster to search for data within JSON structures.
• Flexible data structure: You can store nested JSON objects, arrays, or even dynamic fields that change between records. This is particularly useful in applications where not every record has the same structure.
• Rich querying capabilities: PostgreSQL provides many built-in functions and operations to work with JSONB, such as extracting values from JSON paths, checking containment, and performing deep comparisons.

Why Use JSONB in PostgreSQL with Spring Boot?

In many real-world applications, there's often a need to store semi-structured or dynamic data that does not fit well into a fixed relational schema. This is common in:

• E-commerce applications where product specifications can vary widely between categories.
• Logging systems where logs can have different levels of detail depending on the context.
• Applications that rely on external APIs and need to store diverse and evolving data.

By using PostgreSQL's JSONB type, we can leverage the power of the relational database for structured data while storing flexible, schema-less JSON data within the same database. This allows for powerful capabilities without sacrificing the relational integrity of other parts of the application.

Spring Boot with JPA makes it easy to work with JSONB by directly mapping JSON data to Java objects, making it intuitive for developers to store and query JSON data alongside traditional relational data.

When to Use JSONB?

We might want to use JSONB in the following scenarios:

• Storing API data: If you are working with third-party APIs that return JSON, using JSONB allows you to store the data as-is without mapping it to a traditional relational schema.
• Handling evolving schema: In applications where the structure of the data is not always known upfront or can change frequently, JSONB allows you to store the data without constantly altering the database schema.
• Combining relational and non-relational data: You can benefit from both worlds by using relational data for core application features and JSONB for flexible or semi-structured data.

Implementation of Storing PostgreSQL JSONB Using Spring Boot and JPA

Step 1: Create a New Spring Boot Project

Create a new Spring Boot Project using IntelliJ IDEA. Choose the following options:

• Name: Storing-PostgreSQL-JSONB-Demo
• Language: Java
• Type: Maven
• Packaging: Jar

Click on the Next button.

Step 2: Add the Dependencies

Add the following dependencies to the Spring Boot Project:

• Spring Web
• PostgreSQL Driver
• Spring Boot DevTools
• Lombok
• Spring Data JPA

Click on the Create button.

Project Structure

After the project creation done successfully, the project folder structure will look like the below image:

Step 3: Configure Application Properties

Open the application.properties file and add the following PostgreSQL and Hibernate configuration:

spring.application.name=Storing-PostgreSQL-JSONB-Demo

# PostgreSQL database configuration
spring.datasource.url=jdbc:postgresql://localhost:5432/exampledb
spring.datasource.username=postgres
spring.datasource.password=mypassword
spring.jpa.hibernate.ddl-auto=update
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect

Step 4: Create the Product Class

package com.gfg.storingpostgresqljsonbdemo;

import com.fasterxml.jackson.annotation.JsonIgnoreProperties;
import com.fasterxml.jackson.databind.JsonNode;
import jakarta.persistence.*;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * Entity class representing a Product.
 */
@Data
@Entity
@Table(name = "products")
@JsonIgnoreProperties(ignoreUnknown = true) // Ignore unknown JSON properties
@AllArgsConstructor
@NoArgsConstructor
public class Product {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY) // Auto-increment primary key
    private Long id;

    private String name; // Name of the product

    @Column(columnDefinition = "jsonb") // Specify JSONB column type
    private JsonNode attributes; // Attributes stored as JSON
}

This class defines a Product entity with fields for id, name, and attributes. The attributes field is mapped to the JSONB data type in PostgreSQL.

Step 5: Create the ProductRepository Interface

package com.gfg.storingpostgresqljsonbdemo;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

/**
 * Repository interface for Product entity.
 */
@Repository
public interface ProductRepository extends JpaRepository<Product, Long> {
    // Inherits CRUD operations from JpaRepository
}

This interface extends JpaRepository, enabling standard CRUD operations for the Product entity.

Step 6: Create the JacksonConfig Class

package com.gfg.storingpostgresqljsonbdemo;

import com.fasterxml.jackson.databind.ObjectMapper;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

/**
 * Configuration class for Jackson ObjectMapper.
 */
@Configuration
public class JacksonConfig {
    @Bean
    public ObjectMapper objectMapper() {
        return new ObjectMapper(); // Create and configure ObjectMapper bean
    }
}

This class configures a Jackson ObjectMapper bean for converting JSON data to and from Java objects.

Step 7: Create the ProductRequest Class

package com.gfg.storingpostgresqljsonbdemo;

import com.fasterxml.jackson.databind.JsonNode;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * DTO class for creating a new Product request.
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
class ProductRequest {
    private String name; // Name of the product
    private JsonNode attributes; // JSON attributes of the product
}

This class serves as a Data Transfer Object (DTO) for handling product creation requests, containing fields for name and attributes.

Step 8: Create the ProductService Class

package com.gfg.storingpostgresqljsonbdemo;

import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.springframework.stereotype.Service;

import java.io.IOException;
import java.util.Optional;

/**
 * Service class for Product-related operations.
 */
@Service
public class ProductService {

    private final ProductRepository productRepository;
    private final ObjectMapper objectMapper;

    public ProductService(ProductRepository productRepository, ObjectMapper objectMapper) {
        this.productRepository = productRepository;
        this.objectMapper = objectMapper;
    }

    // Method to save a product
    public Product saveProduct(ProductRequest productRequest) throws IOException {
        Product product = new Product();
        product.setName(productRequest.getName());
        JsonNode jsonNode = objectMapper.readTree(productRequest.getAttributes().traverse()); // Parse JSON attributes
        product.setAttributes(jsonNode);
        return productRepository.save(product); // Save product to the database
    }

    // Method to get a product by ID
    public Optional<Product> getProduct(Long id) {
        return productRepository.findById(id); // Retrieve product from the database
    }
}

This service class contains methods to save a Product and retrieve it by ID. It uses ObjectMapper to parse the JSON attributes.

Step 9: Create the ProductController Class

package com.gfg.storingpostgresqljsonbdemo;

import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;

import org.springframework.http.HttpStatus;

/**
 * Controller class for managing Product endpoints.
 */
@RestController
@RequestMapping("/products") // Base URL for product endpoints
public class ProductController {

    private final ProductService productService;

    public ProductController(ProductService productService) {
        this.productService = productService;
    }

    // POST request to create a new product
    @PostMapping
    public ResponseEntity<String> createProduct(@RequestBody ProductRequest productRequest) {
        try {
            productService.saveProduct(productRequest); // Save product
            return ResponseEntity.status(HttpStatus.CREATED).body("Product created successfully"); // Return response
        } catch (Exception e) {
            return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).body("Failed to create product"); // Handle errors
        }
    }

    // GET request to retrieve a product by ID
    @GetMapping("/{id}")
    public ResponseEntity<Product> getProduct(@PathVariable Long id) {
        return productService.getProduct(id)
                .map(ResponseEntity::ok) // Return product if found
                .orElse(ResponseEntity.notFound().build()); // Return 404 if not found
    }
}

This controller class defines RESTful endpoints for creating and retrieving products. It handles requests and interacts with the ProductService.

Step 10: Main class

No changes are required in the main class.

package com.gfg.storingpostgresqljsonbdemo;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

/**
 * Main class to run the Spring Boot application.
 */
@SpringBootApplication
public class StoringPostgreSQLJsonBDemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(StoringPostgreSQLJsonBDemoApplication.class, args); // Start the application
    }
}

This is the main class of the Spring Boot application, responsible for launching the application.

pom.xml File:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>3.3.4</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.gfg</groupId>
    <artifactId>Storing-PostgreSQL-JSONB-Demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>Storing-PostgreSQL-JSONB-Demo</name>
    <description>Storing-PostgreSQL-JSONB-Demo</description>
    <url/>
    <licenses>
        <license/>
    </licenses>
    <developers>
        <developer/>
    </developers>
    <scm>
        <connection/>
        <developerConnection/>
        <tag/>
        <url/>
    </scm>
    <properties>
        <java.version>17</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <scope>runtime</scope>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.postgresql/postgresql -->
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>42.7.4</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

Step 11: Run the Application

Now, run the application. Make sure your PostgreSQL server is up and running, and the database (exampledb) is created.

Step 12: Testing the Application

We can use Postman or any other API client to test the application.

Note: Here we will be using the Postman tool.

1. Create the Product

POST http://localhost:8080/products

Response:

2. Get the Product By ID

GET http://localhost:8080/products/{id}

Replace {id} with the ID of the product you created.

Response:

This example project demonstrates storing PostgreSQL JSONB using the Spring Boot and JPA .

Conclusion

By using the PostgreSQL's JSONB data type and mapping it to the JsonNode in the Spring Boot, we can efficiently store and manage the semi-structured JSON data. This approach allows for the dynamic fields, complex querying, and indexing, making it perfect for applications requiring the flexible data models.