I am working on a Spring Boot project that uses PostgreSQL. I want to create a advanced search feature where user can search through the jsonb column that I have in many of my tables.
Somewhere in my Specification class, I have this code:
...
String stringToSearch = searchCriteria.getValue().toString().toLowerCase();
String filterKey = searchCriteria.getFilterKey();
Expression<String> columnPath;
if(filterKey.contains(".")){
columnPath = root.get(filterKey.substring(0, filterKey.indexOf(".")));
System.out.println(filterKey.substring(filterKey.indexOf(".")+1));
String[] jsonPaths = (filterKey.substring(filterKey.indexOf(".")+1)).split("\.");
Expression<String> totalJsonPath = cb.literal(jsonPaths[0]);
for(String jsonPath: jsonPaths){
totalJsonPath = cb.and(totalJsonPath, cb.literal(jsonPath));
}
columnPath = cb.function("jsonb_extract_path_text", String.class, columnPath, totalJsonPath);
} else {
columnPath = root.get(filterKey);
}
switch(Objects.requireNonNull(SearchOperation.getSimpleOperation(searchCriteria.getOperation()))){
case CONTAIN:
return cb.like(columnPath, "%" + stringToSearch + "%");
case DOES_NOT_CONTAIN:
return cb.notLike(columnPath, "%" + stringToSearch + "%");
...
I want it this way because then user can search deep inside the jsonb, eg:
"jsonCol":{
"jsonKey": {
"jsonNestedKey" : "my_value"
}
}
I know my code above does not work because cb.and() is not for this purpose. What I want to achieve is:
columnPath = cb.function("jsonb_extract_path_text",
String.class,
columnPath,
cb.literal(jsonPaths[0]),
cb.literal(jsonPaths[1])
);
While chaining 2 "jsonb_extract_path_text" function with cb.and() defeats the purpose, I have also tried nesting 2 "jsonb_extract_path_text" functions inside of each other but PostgreSQL does not understand what I am trying to do.
You have my thanks in advanced.