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
