This is a really valuable idea. It will work in some situations for me. But in other situations I do not know if table will have a key of type int[] or string[] or even mixed. That’s why I’d wish to use JSON arrays and customize sort ordering.
In my situation this order is invalid. Obviously, year 2016 should go after 2014, like that:
I think you expect JSONB to sort differently than it does. I cannot imagine what a "natural" ordering of arbitrary JSON objects is.
FWIW, Postgres arrays do sort in the way he's expecting:
paul=# create table t (id integer, v integer[]); CREATE TABLE paul=# insert into t values (1, array[2014]), (2, array[2014, 1]), (3, array[2016]); INSERT 0 3 paul=# select * from t order by v; id | v ----+---------- 1 | {2014} 2 | {2014,1} 3 | {2016} (3 rows)
