jsonb sort: convert to text

[itineric]

When sorting on a jsonb sub-field, the function jsonb_extract_path is used. This function returns a jsonb result type. When sorting 'ic' (case insensitive), lower function is used. But lower function expects 'text' as argument and does not work on jsonb. The returned error is: No function matches the given name and argument types. You might need to add explicit type casts.

My proposal is: when you know an 'ic' comparison is expected, you could use jsonb_extract_path_text function instead of jsonb_extract_path, then lower function works.

How to fix it in SortUtils.java:

Add boolean parameter caseInsensitive to method sortExpressionOfJson. Use boolean to return jsonb_extract_path_text function:

if (caseInsensitive) {
  return builder.function("jsonb_extract_path_text", String.class, args.toArray(Expression[]::new));
} else {
  return builder.function("jsonb_extract_path", String.class, args.toArray(Expression[]::new));
}

In method sortToJpaOrder, before calling sortExpressionOfJson, find out if 'ic' is expected: final boolean caseInsensitive = parts.length > 2 && "ic".equalsIgnoreCase(parts[2]);