It looks like SQLite doesn't use numeric-ordering when extracting a number from JSON. Could it?

SQLite version 3.39.4 2022-09-29 15:55:41
Enter ".help" for usage hints.
sqlite> SELECT json -> '$."6"', json_type(json -> '$."6"') from testjson;
0|integer
-1|integer
-1000|integer
534|integer
sqlite> SELECT json -> '$."6"', json_type(json -> '$."6"') from testjson ORDER BY (json -> '$."6"') ASC;
-1|integer
-1000|integer
0|integer
534|integer
sqlite> SELECT json -> '$."6"', json_type(json -> '$."6"') from testjson ORDER BY (json -> '$."6"') + 0 ASC;
-1000|integer
-1|integer
0|integer
534|integer

Have you tried using ->> to get the actual number, and not text?

The -> and ->> operators

Thanks, that works!

Unfortunately, it looks like it works the opposite way for Postgresql:

test_django=# SELECT id, value FROM model_fields_nullablejsonmodel ORDER BY value -> 'ord' ASC;
 id |             value            

----+-------------------------------
 96 | {"ord": -1000, "name": "bot"}
 95 | {"ord": -1, "name": "baz"}
 94 | {"ord": 22.1, "name": "foo"}
 93 | {"ord": 93, "name": "bar"}
(4 rows)


test_django=# SELECT id, value FROM model_fields_nullablejsonmodel ORDER BY value ->> 'ord' ASC;
 id |             value            

----+-------------------------------
 95 | {"ord": -1, "name": "baz"}
 96 | {"ord": -1000, "name": "bot"}
 94 | {"ord": 22.1, "name": "foo"}
 93 | {"ord": 93, "name": "bar"}
(4 rows)

It appears SQLite is treating JSON numbers as strings, at least when it comes to sorting them:

sqlite> create table test(value);
sqlite> insert into test values ('{"ord":3}'), ('{"ord":100}'), ('{"ord":20}');
sqlite> select value from test order by value -> 'ord' asc;
sqlite> select value from test order by value -> 'ord' asc;
{"ord":100}
{"ord":20}
{"ord":3}
sqlite> select value from test order by cast(value -> 'ord' as int) asc;
{"ord":3}
{"ord":20}
{"ord":100}

No. The ->> operator in PG always gives a TEXT result. (See https://www.postgresql.org/docs/15/functions-json.html.) Your ->> output is being sorted in in TEXT order, not in numeric order. The -> operator in PG always returns JSON. I don't know what PG considers JSON sort order to be, but I suspect that if you added a new field in front of "ord", it would mess up your sort.

The fact that the PG ->> operator always returns TEXT, rather than the type of the underlying JSON object, is a limitation in PG that derives from its rigid type system. In PG, the return type of an operator is always determined by the type of its operands, so that the type can be computed at compile-time. To my mind, this is a bug in PG.

SQLite computes datatypes at run-time, not a compile-time. Hence, the ->> operator in SQLite can (and does) return values of different types depending on the values of its operands, not just the datatype of its operands. Hence, SQLite really is able to return an INT or REAL or TEXT value from ->> if the field being selected is an integer or real or text value.

Yes. SQLite sorts JSON values according to the string representation of the JSON. How else do you propose to sort JSON?

In Postgres, json has no sort order, if you try you get

ERROR: could not identify an ordering operator for type json

jsonb though does have a sort order that's listed at the bottom of the jsonb Indexing section

jsonb also supports btree and hash indexes. These are usually useful only if it's important to check equality of complete JSON documents. The btree ordering for jsonb datums is seldom of great interest, but for completeness it is:

Object > Array > Boolean > Number > String > Null

Object with n pairs > object with n - 1 pairs

Array with n elements > array with n - 1 elements

Objects with equal numbers of pairs are compared in the order:

key-1, value-1, key-2 ...

Note that object keys are compared in their storage order; in particular, since shorter keys are stored before longer keys, this can lead to results that might be unintuitive, such as:

{ "aa": 1, "c": 1} > {"b": 1, "d": 1}

Similarly, arrays with equal numbers of elements are compared in the order:

element-1, element-2 ...

Primitive JSON values are compared using the same comparison rules as for the underlying PostgreSQL data type. Strings are compared using the default database collation.

In Postgres, json has no sort order, if you try you get "ERROR: could not identify an ordering operator for type json"

That's not what is happening for Ben in post #6 above. He has an ORDER BY on the result of the -> operator (which is JSON according to the documentation) and it appears to work and give the answer that he wants.

Yup, sounds right.

And I did get the "ERROR: could not identify an ordering operator for type json" issue when I declared the field as JSON instead of JSONB.