Most of us have built numerous Spring Boot applications that perform standard CRUD (Create, Read, Update, Delete) operations on structured data stored in relational databases such as PostgreSQL. These systems typically rely on fixed schemas and well-defined table structures. But, what happens when your use case requires storing and manipulating unstructured, schema-less JSON documents inside the RDBMS tables?
I have been exploring effective strategies to store, update, retrieve and delete unstructured, schema-less JSON data in PostgreSQL tables using Spring Boot. Today, I’m excited to share these insights with you. This feature was introduced by PostgreSQL when NoSQL databases were getting a lot of popularity over traditional RDBMS systems. While many developers might be aware of the fact that PostgreSQL supports JSON and JSONB types, the nuances—such as partial updates using functions like jsonb_set()—are less commonly implemented.
By the end of this article, we will have a fully functional REST controller with CRUD endpoints capable of Storing, Reading, Updating and Deleting JSON data in PostgreSQL database using Spring Boot. This includes updating specific key-value pair of JSON, adding a new key-value pair into the JSON, and removing a key-value pair from JSON etc using functions like to_json() and json_set() for a fine-grained JSON manipulation.
Here is the link to a Spring Boot application that I wrote.
Let’s CODE JSON in POSTGRESQL!
- Navigate to https://start.spring.io/ and create a Spring Boot project with the below-mentioned dependencies.
2. Spin up a PostgreSQL docker container to connect with our Spring Boot application by following steps 1–4 from here.
3. Update the application.properties as mentioned below. Because our goal is to persist JSON data into the Postgresql database, hence, we specify “stringtype=unspecified” in spring.datasource.url field.
spring.datasource.url = jdbc:postgresql://localhost:5432/postgres?stringtype=unspecified
spring.datasource.username = postgres
spring.datasource.password = password
spring.datasource.platform = postgresqlBelow line creates a Student table using JPA entity and updates it if it already exists. (Doesn’t reset it on next time run)
spring.jpa.hibernate.ddl-auto = update
spring.jpa.properties.hibernate.show_sql=true
server.port=80814. Create entity classes as below. Student table has a jsonb type column favs. You can read more about jsonb datatype on the web, but it’s the same as json with slight improvements. One of the main advantages of using jsonb over json in our case is that jsonb type data can make use of functions like jsonb_set() available in PostgreSQL which helps us in updating the key-value pairs in json (not entire json). If you wish, you can make use of JSON type column along with the json_set() function in MySQL.
@Entity
@Getter
@Setter
@ToString
//@TypeDefs({
// @TypeDef(name = "jsonb", typeClass = JsonBinaryType.class)
//})
public class Student implements Serializable {
private int id;
private String name;
private int age;
//Below will map the city property of the Address class to city column in the Student database table. Its not needed, if names are same.
@EmbeddedId
@AttributeOverrides({
@AttributeOverride( name = "city", column = @Column(name = "city"))})
private Address address;
private boolean adult;
//@Type(type = "jsonb")
@Column(columnDefinition = "jsonb")
private String favs;
}
Address has 2 variables: City and State. Both, City and State have been marked as composite Primary Key for the Student table using Embeddable and EmbeddedId
@Setter
@Getter
@Embeddable
@ToString
@AllArgsConstructor
@NoArgsConstructor
public class Address implements Serializable {
private String city;
private String state;
}
5. Create the JPA Repository class as mentioned below. Try to spend some time and analyse the custom queries that I have written. If required, use this PostgreSQL documentation in order to understand the functions like to_json() and json_set().
@Repository
public interface StudentJpaRepository extends JpaRepository<Student, Address> {
List<Student> findByName(String name);
@Query(value = "select * from student where favs->>?1 = ?2", nativeQuery = true)
List<Student> findByFavs(String key, String value);
//String query1 = "update student set favs= jsonb_set(favs , '{color}' , '\"darkgreen\"') where id=1";
//We dont need single quotes in java in case we want to fill values dynamically using @Param.
//jsonb_set takes column_name, key, value and boolean as arguments. Boolean signifies whether to create a new field if it already doesnt exist.
String query1 = "update student set favs= jsonb_set( favs , CAST(CONCAT('{', CAST(:key AS text), '}') as text[]), to_jsonb(CAST(:value AS text)), true) where id=:id";
@Modifying
@Query(value = query1, nativeQuery = true)
void updateStudentFavsKeyValueOrCreateIfNotExists(@Param("key") String key, @Param("value") String value, @Param("id") int id);
//String query2 = "update student set favs= jsonb_set(favs , '{attempts}' , '25') where id=1";
String query2 = "update student set favs= jsonb_set( favs , CAST(CONCAT('{', CAST(:key AS text), '}') as text[]), to_jsonb(CAST(:value AS int)), true) where id=:id";
@Modifying
@Query(value = query2, nativeQuery = true)
void updateStudentFavsKeyValueOrCreateIfNotExistsIntType(String key, int value, int id);
@Modifying
@Query(value = "update student set favs= ?1 where id=?2", nativeQuery = true)
void updateStudentFavs(String favs, int id);
int deleteByName(String name);
@Modifying
@Query(value = "UPDATE student SET favs = favs - CAST(?1 as text) WHERE id = CAST(?2 as int);", nativeQuery = true)
void deleteFavsKeyValuePair(String keyName, int id);
}
6. Develop the service interface and implementation to encapsulate business logic. Here is a glimpse of it:
public interface StudentService {
Student addStudent(Student student) throws JsonProcessingException;
List<StudentRequest> getAllStudents() throws JsonProcessingException;
List<StudentRequest> getStudentsByName(String name) throws JsonProcessingException;
StudentRequest getStudentsById(String city, String state) throws JsonProcessingException;
List<StudentRequest> getStudentsByFavs(String key, String value) throws JsonProcessingException;
void updateStudentFavsKeyValueOrCreateIfNotExists(String key, String value, int id);
void updateStudentFavsKeyValueOrCreateIfNotExistsIntType(String key, int value, int id);
void updateStudentFavs(String favs, int id);
int deleteStudent(String name);
void deleteStudentFavsKeyValue(String keyName, int id);
}
@Service
@Slf4j
public class StudentServiceImpl implements StudentService {
private StudentJpaRepository studentJpaRepository;
@Autowired
StudentServiceImpl( StudentJpaRepository studentJpaRepository){
this.studentJpaRepository = studentJpaRepository;
}
@Override
public Student addStudent(Student student) {
return studentJpaRepository.save(student);
}
@Override
public List<StudentRequest> getAllStudents() throws JsonProcessingException {
List<Student> studentList = studentJpaRepository.findAll();
return getStudentRequestList(studentList);
}
@Override
public List<StudentRequest> getStudentsByName(String name) throws JsonProcessingException {
List<Student> studentList = studentJpaRepository.findByName(name);
return getStudentRequestList(studentList);
}
@Override
public StudentRequest getStudentsById(String city, String state) throws JsonProcessingException {
Optional<Student> studentOptional = studentJpaRepository.findById(new Address(city, state));
if(!studentOptional.isPresent()){
return null;
}
Student s = studentOptional.get();
return getStudentRequestList(Collections.singletonList(s)).get(0);
}
@Override
public List<StudentRequest> getStudentsByFavs(String key, String value) throws JsonProcessingException {
List<Student> studentList = studentJpaRepository.findByFavs(key, value);
if(studentList == null || studentList.isEmpty()){
return null;
}
List<StudentRequest> studentRequestList = getStudentRequestList(studentList);
return studentRequestList;
}
@Transactional
@Override
public void updateStudentFavsKeyValueOrCreateIfNotExists(String key, String value, int id) {
log.info("Update request received by StudentServiceImpl for updating: {} to {} for ID={}", key, value, id);
studentJpaRepository.updateStudentFavsKeyValueOrCreateIfNotExists(key, value, id);
}
@Transactional
@Override
public void updateStudentFavsKeyValueOrCreateIfNotExistsIntType(String key, int value, int id) {
log.info("Update request received by StudentServiceImpl for updating: {} to {} for ID={}", key, value, id);
studentJpaRepository.updateStudentFavsKeyValueOrCreateIfNotExistsIntType(key, value, id);
}
@Transactional
@Override
public void updateStudentFavs(String favs, int id) {
studentJpaRepository.updateStudentFavs(favs, id);
}
@Transactional
@Override
public int deleteStudent(String name) {
return studentJpaRepository.deleteByName(name);
}
@Transactional
@Override
public void deleteStudentFavsKeyValue(String keyName, int id) {
studentJpaRepository.deleteFavsKeyValuePair(keyName, id);
}
private List<StudentRequest> getStudentRequestList(List<Student> studentList) throws JsonProcessingException {
List<StudentRequest> studentRequestList = new ArrayList<>();
for (Student student: studentList) {
ObjectMapper objectMapper = new ObjectMapper();
StudentRequest studentRequest = new StudentRequest();
studentRequest.setId(student.getId());
studentRequest.setName(student.getName());
studentRequest.setAge(student.getAge());
studentRequest.setCity(student.getAddress().getCity());
studentRequest.setState(student.getAddress().getState());
studentRequest.setAdult(student.isAdult());
studentRequest.setFavs(objectMapper.readValue(student.getFavs(), HashMap.class));
studentRequestList.add(studentRequest);
}
return studentRequestList;
}
}
7. Lastly, Expose REST endpoints for CRUD operations using a Controller class. Here is a glimpse of it:
@RestController
@Slf4j
public class StudentController {
private RestTemplate restTemplate = new RestTemplate();
private StudentService studentService;
@Autowired
public StudentController(StudentService studentService) {
this.studentService = studentService;
}
@PostMapping("/student")
private Student addStudent(@RequestBody StudentRequest studentReq) throws JsonProcessingException {
log.info("Student to be added is {}", studentReq);
Student student = getStudent(studentReq);
ObjectMapper objectMapper = new ObjectMapper();
student.setFavs(objectMapper.writeValueAsString(studentReq.getFavs()));
return studentService.addStudent(student);
}
@GetMapping("/student")
private List<StudentRequest> getAllStudent() throws Exception {
return studentService.getAllStudents();
}
//Name is one of the columns in our table. So we can use it to query.
@GetMapping("/student/filterByName/{name}")
private List<StudentRequest> getStudentsByName(@PathVariable String name) throws JsonProcessingException {
return studentService.getStudentsByName(name);
}
//Our table has a Primary Key of City, State. So we need to pass both city and state to get the student by Id.
@GetMapping("/student/filterById")
private StudentRequest getStudentsById(@RequestParam String city, @RequestParam String state) throws JsonProcessingException {
return studentService.getStudentsById(city, state);
}
//favs is a jsonb column in our table. We may want to filter favs by a specific key and value.
@GetMapping("/student/filterByJsonColumnKeyValue/{key}/{value}")
private List<StudentRequest> getStudentsByFavs(@PathVariable String key, @PathVariable String value) throws JsonProcessingException {
return studentService.getStudentsByFavs(key, value);
}
//update a specific key in json column favs to a new String type value. If key doesnt exist, create it.
@PatchMapping("/student/updateFavs/{key}/{value}/{id}")
private void updateStudentFavsKeyValueOrCreateIfNotExists(@PathVariable String key, @PathVariable String value, @PathVariable int id) {
studentService.updateStudentFavsKeyValueOrCreateIfNotExists(key, value, id);
}
//update a specific key in json column favs to a new Int type value. If key doesnt exist, create it.
@PatchMapping("/student/updateFavsIntType/{key}/{value}/{id}")
private void updateStudentFavsKeyValueOrCreateIfNotExistsIntType(@PathVariable String key, @PathVariable int value, @PathVariable int id) {
studentService.updateStudentFavsKeyValueOrCreateIfNotExistsIntType(key, value, id);
}
//Update the json column favs entry entirely. This will replace the existing favs column entry with the new one for specified ID.
@PatchMapping("/student")
private void updateStudent(@RequestBody StudentRequest studentReq) throws JsonProcessingException {
Student student = getStudent(studentReq);
ObjectMapper objectMapper = new ObjectMapper();
student.setFavs(objectMapper.writeValueAsString(studentReq.getFavs()));
studentService.updateStudentFavs(student.getFavs(), student.getId());
}
//Delete a student record by name.
@DeleteMapping("/student/{name}")
private int deleteStudent(@PathVariable String name) {
return studentService.deleteStudent(name);
}
//Our table has json column. We can remove specific key-value pair from the json column.
@DeleteMapping("/student/keyValue/{key}/{id}")
private void deleteStudentFavsKeyValue(@PathVariable String key, @PathVariable int id) {
studentService.deleteStudentFavsKeyValue(key, id);
}
private static Student getStudent(StudentRequest studentReq) {
Student student = new Student();
student.setName(studentReq.getName());
student.setAge(studentReq.getAge());
Address address = new Address();
address.setCity(studentReq.getCity());
address.setState(studentReq.getState());
student.setAddress(address);
student.setId(studentReq.getId());
student.setAdult(studentReq.isAdult());
return student;
}
}
8. In order to take requests from user and submit the request to Controller, we have to create a Domain class called StudentRequest. Let’s Define a StudentRequest class that maps incoming payloads to our domain model.
@Getter
@Setter
@ToString
@JsonIgnoreProperties(ignoreUnknown = true)
public class StudentRequest {
private int id;
private String name;
private int age;
private String city;
private String state;
private boolean adult;
private Map<String, Object> favs;
}
That’s it. We are all set.
Running and Testing the APPLICAtion
Now, Build the Spring Boot application:
mvn clean install
Then, Run the Spring Boot application:
mvn spring-boot:run
With both PostgreSQL and the Spring Boot app running, we can now test the APIs using Postman.
Download the Postman collection JSON file from my Github. Open Postman and import the Postman collection named “JPAwithJSON.postman_collection.json”.
Try to hit Create a new Student API, Get all Students API, etc
This demonstrates that modern RDBMS platforms like PostgreSQL are more than capable of managing semi-structured JSON data without forcing a move to NoSQL solutions.
If you found this article helpful, feel free to share it and don’t forget to connect with me on Linkedin.
This article is part of the JAVAPRO magazine issue:
Engineering Intelligence – Building Systems in the AI Age
Explore what engineering means in the AI age — beyond code generation and automation.
Gain insights into performance-critical Java systems, evolving architectures, and the responsibilities that come with building intelligent software.
Discover the edition →
