Hi All, I am using springboot and spring-data-jpa to create a rest-api that handles crud operations. I am using Postgresql as the DB. I can get it to work fine, however given the JSON example below I want to store the phoneNumbers, emails, addresses, urls, settings as JSON in the DB column for each one respectively. Is there a known way that this can be done? My data class is fairly simple: I have tried String, Map and Any as the data type for those fields none of which works.

@Table("organisations")
data class Organisation(
    @Id val id: UUID?,
    val name: String = "",
    val information: String = "",
    val urls: Any?,
    val emails: Any?,
    val phoneNumbers: Any?,
    val addresses: Any?,
    val settings: Any?,
    val active: Boolean = true,
    val createdAt: Instant = Instant.now(),
    val updatedAt: Instant = Instant.now(),
)

{ “name”: “Business Name”, “information”: “Business Info”, “phoneNumbers”: { “office”: “+6172345678”, “imam”: “+6142349876” }, “emails”: { “info”: “info@bus.com”, “commity”: “commity@bus.com” }, “addresses”: { “address”: “2345 SomeStreet Road, MyTOWN, 1234", “postal address”: “PO Box 123" }, “urls”: {}, “settings”: { “timeFormat”: “dd/MM/yyyy”, “currency”: “AUD” } } Any pointers or help would be greatly appreciated. Thanks

Well, json is just a string, so I imagine that should work. Why didnt that work for you?

Thanks for the response. I get the following error when the request is deserialized: Resolved [org.springframework.http.converter.HttpMessageNotReadableException: JSON parse error: Cannot deserialize value of type

java.lang.String

from Object value (token

JsonToken.START_OBJECT

); nested exception is com.fasterxml.jackson.databind.exc.MismatchedInputException: Cannot deserialize value of type

java.lang.String

from Object value (token

JsonToken.START_OBJECT

) at [Source: (PushbackInputStream); line: 4, column: 21] (through reference chain: com.masjidlink.api.Organisation[“phoneNumbers”])]

ah ok 🙂 I think I see what is happening here. Basicalliy what is going on is that you use the same object for a database entity and as a json object. This can sometimes indeed cause issues (on multiple fronts, even security).

So basically a DTO that contains for example a list of address objects and then I translate that to the format I want for the data class?

you should make a OrganisationAPI class and a OrganisationEntity class (both can be data classes) and use the api class in the @RequestBody and use the @Table on the OrganisationEntity class

I understand. That makes sense. Thanks for the help. I was trying to avoid duplicating code but I guess sometimes you can’t.

In this case I basically always have different classes for API and entity, yes it may sound as duplication at first, but in practice there are a lot of reasons why you want to have separate classes for this; Sometimes (quite often) you do not want the same information (for example you might not want to expose the password field in a user object), sometimes the types don't match up (like you are experiencing), you dont always want to change the service API everytime you have a database change (or vice-versa) for backwards compatibility etc... so in practice in most cases it is good to have 2 classes for this, even if it means a bit of duplication

Thanks for the help. That makes perfect sense.

Happy to help 🙂

Much appreciated. I will.

Hi, you can also make a use of this repository -> https://github.com/vladmihalcea/hibernate-types With this dependency you can simply mark column as

jsonb

type (which is generally speaking

json

optimized for certain operations such as exctracting the value of object associated with some key) in your entity class.

@Entity
@TypeDef(name = "jsonb", typeClass = JsonBinaryType.class)
class Foo(
    @Id val id: UUID,
    @Type(type = "jsonb")
    @Column(columnDefinition = "jsonb")
    val bar: Bar
)

Thanks @Łukasz Pięta. Sorry, I missed this. I am trying to avoid using Hibernate.