0

Got a table in a postgres database with a jsonb column and needed to order a query using a specific key in the json as a numeric value, it is possible with native sql:

select key->>value from table where order by CAST(key->>value AS DECIMAL);

But with specification it is no possible directly, so I've encapsulate the operator CAST in a function and call it when building the order clause in the specification factory.

create or replace function cast_number(str varchar)
   returns real
   language plpgsql
  as
$$
declare 
new_number real;
begin
 select cast(str as decimal)
 into new_number;
 return new_number;
end;

and I call it using CriteriaBuilder::function after calling jsonb_extract_path_text also via criteria builder. Still no sure about the performance here tbh

Did not found anything about this anywhere, so I want to share my solution and see what people think of it.

Riberto Junior
  • 171
  • 1
  • 8

1 Answers1

1

Yes. What you want is possible with native sql and is possible directly. I am not sure where you thought directly was not possible but perhaps from your query itself. It is invalid and would be so even without the data being Json. You have a where clause but no predicates after it. The structure where order... is invalid. But a direct cast would in order by is valid whit a valid where clause. (see demo).
As far as encapsulating the cast in a function there is nothing wrong with doing so. However, you can substantially reduce the complexity by creating a SQL function instead of an pgplsql one, and has other advantages; see selected answer. Your function rewritten as SQL function: (also in demo)

create or replace function cast_number(str varchar)
    returns decimal
    immutable
    language sql
as $$
   select cast(str as decimal);
$$;

NOTE:

  • immutable added but not essential (check it out)
  • changed return type to match cast. Not required but a good habit to form matching what is actually returned to the declared return type.
Belayer
  • 13,578
  • 2
  • 11
  • 22