how to store JSON in POSTGRES using SpringBoot + JPA?

Question

I have a payload that am trying to save into a database, whenever I post the payload, it returns the mapped JSON objects as null instead of the values in the payload passed.

here is the JSON payload

{
 "authStatus": {
 "authStatusCode": 131,
 "authStatusDescription": "API call doesn't need authentication"
 },
 "results": {
 "beepTransactionID": 10764659,
 "payerTransactionID": "5f51eed7347a7",
 "statusCode": "188",
 "statusDescription": "Response was received"
 }
}

after posting it to postman

{
 "id": 14,
 "authStatusCode": null,
 "authStatusDescription": null,
 "beepTransactionID": null,
 "payerTransactionID": null,
 "statusCode": null,
 "statusDescription": null
}

my controller for posting

 @PostMapping("/payload")
 public Payload createPayload(@Valid @RequestBody Payload payload) {
 return payloadRepository.save(payload);
 }

my model

@Entity
@Table(name = "payload", schema = "public")
 public class Payload {
 private long id;
 Integer authStatusCode;
 String authStatusDescription;
 Integer beepTransactionID;
 String payerTransactionID;
 String statusCode;
 String statusDescription;
 
 public Payload(){
 
 }
 
 //getters and setters

• venkat: Are you having three tables one for payload, one for Authstatus and another for results or else its just the same
• Raymond Mumo: Hi @venkat just the same table

Answer 1

You need intermediary objects, Payload needs to be a bit deeper to match exactly the format of your JSON object

public class Payload {
 AuthStatus authStatus;
 Results results;
 // Add boilerplate code here
}
public class AuthStatus {
 Integer authStatusCode;
 String authStatusDescription;
 // Add boilerplate code here
}
public class Results {
 Integer beepTransactionID;
 String payerTransactionID;
 String statusCode;
 String statusDescription;
 // Add boilerplate code here
}

• Raymond Mumo: Hi @Yassin and where do I set the getters and setters for the above and how do I call save?
• Yassin Hajaj: Raymond, add the getters and setters yourself no problem with that. The saving mechanism remains the same.
• Raymond Mumo: public class AuthStatus and public class Results are complaining that I dnt make them public, if they are public they are throwing an error
• Yassin Hajaj: Create them in their own file
• Raymond Mumo: Which class will have the entity annotation and table name? And which one will house the primary id?

Answer 2

Try using

@Type(type = "jsonb")
@Column(columnDefinition = "jsonb")

to store payload into JsonNode format as it is

• Raymond Mumo: hi @dhiraj, I want to extract the specific values from the json string and insert to columns ands rows, not the entire json