Re: BUG #18564: Incorrect sorting with using NULLS LAST (NULLS FIRST)

David G. Johnston wrote:

> In short, while this can be controlled at the SQL scope, the comparison
> operator for the json data type provides no such ability for the user to
> control the result of the comparison between null and non-null json
> values.

It looks like a good use case for NULLIF.

SELECT example_jsonb_column->'sorting_param'
FROM example_table
ORDER BY NULLIF(example_jsonb_column->'sorting_param', 'null'::jsonb)
ASC NULLS LAST ;

The doc says that primitive JSON values are compared using the same
comparison rules as for the underlying PostgreSQL data type, so the
non-null values will be sorted as "numeric", which meets the OP's
expectations:

> Expected Behavior:
> NULL values should appear at the end of the sorted results, and the sorting
> should be accurate based on the numeric values.

Best regards,
--
Daniel Vérité
https://postgresql.verite.pro/
Twitter: @DanielVerite