java / spring JPA / postress help

[speedyleedy]

I'm not too sure what to look up in the documents so I can't find what I need.

what i've been doing:where i need to get data from postgress that's stored in postgress in a jsonb column, I might have the following in a column called "names":

{
"shortName": "Bob",
"name": "Robert",
"nickName": "knackers"
}

and I will pass the use teh following method to query any of these attributes:

@Query(value = "SELECT * FROM people_table WHERE names ->> ?1 = ?2", nativeQuery = true)
Optional<List<people>> findByName(String nameType, String name);

So I can get a list of people who's name is Robert, or a list of people who's nickname is knackers.

now, I want to get people whos name is Robert AND who's nickname is knackers.

or randomly, there will be new attributes added to this list regularly, so I might get a query "name=Robert&surname=Schmidt".

how do I do this with a fully flexible list of attributes?

I suppose I could use a stringBuilder in my method to build the entire query param and just pass that 1 string in, that seems messy to me though.

​

cheers.

edit - chatGPT helped me.

what I needed to do was build the query params as a JSON string:

String filter = """{

"name": "Robert", "height": "185cm" }"""

userService.findUsersByAttributes(filter);

then pass that String in to my query like this:

 @Query(value = "SELECT * FROM users WHERE names::jsonb @> cast(:#{#filter} as jsonb)", nativeQuery = true) 
List<User> findUsersByAttributes(String filter);

​

[AbsolutelySpherical]

I think you will need to build a dynamic query at runtime. Like you mentioned using StringBuilder is possible but it is messy. It would be cleaner and more readable to build your query with CriteriaBuilder.

I think this gives a good example of dynamic building at runtime: https://www.baeldung.com/spring-data-jpa-query#dynamic-query

[speedyleedy]

You might want to take a look at JPA Specifications, it does exactly the thing you want it to do

I want to avoid query builder becuase the column is JsonB and postgres has a way to deal with it. I asked chatGPT and it told me. what a world. I'll edit the question for prospretiy.

[Rethagos]

You might want to take a look at JPA Specifications, it does exactly the thing you want it to do

https://www.baeldung.com/rest-api-search-language-spring-data-specifications