sorting data by "value" from jsonb_each(), is reliable?

Question

Given jsonb type column and data structure like this:

{ "1": 10000.2, "2": 77.2, "3": -200.09, "4": 12.55 }

Need retrieve result from this json and sort by VALUE

SELECT * FROM jsonb_each(
 (SELECT jsonb_column FROM table WHERE id = 777)
) 
ORDER BY  VALUE  DESC 
LIMIT 100

Yes, this gives correct result, but question is, how reliable is ordering by jsonb type? (because VALUE is jsonb type). Will this always work correctly?

I don't want (if not necessary) explicit convert: ORDER BY CAST(VALUE::TEXT AS NUMERIC) because in json, there is ~ 500 000 elements and when converting, it takes 2X time, than sorting just by VALUE

Answer 1

If you don't mix data types for values that your order by, you don't need cast, if you do, it will be ordered as documented.

Object > Array > Boolean > Number > String > Null

Object with n pairs > object with n - 1 pairs

Array with n elements > array with n - 1 elements

so if you try your statement against such json:

{
  "date": "2016-10-10",
  "2": "-200.08",
  "3": -200.09,
  "some": "text", 
  "5":-200.08,
  "mt":"-200.09",
  "ar":[0,2]
}

Your get the order from above:

t=# SELECT *,pg_typeof(value) FROM jsonb_each((select a from jt where i = 7 )) order by value desc;
 key  |    value     | pg_typeof
------+--------------+-----------
 ar   | [0, 2]       | jsonb
 5    | -200.08      | jsonb
 3    | -200.09      | jsonb
 some | "text"       | jsonb
 date | "2016-10-10" | jsonb
 mt   | "-200.09"    | jsonb
 2    | "-200.08"    | jsonb
(7 rows)