sql - 自定义 jsonb 键排序顺序

Question

我在 PostgreSQL 中有一个表，其中包含一些数据:

create table t2 (
    key jsonb,
    value jsonb
);

insert into t2(key, value) values ('1', '"test 1"');
insert into t2(key, value) values ('2', '"test 2"');
insert into t2(key, value) values ('3', '"test 3"');
insert into t2(key, value) values ('[]', '"test 4"');
insert into t2(key, value) values ('[1]', '"test 5"');
insert into t2(key, value) values ('[2]', '"test 6"');
insert into t2(key, value) values ('[3]', '"test 7"');
insert into t2(key, value) values ('[1, 2]', '"test 8"');
insert into t2(key, value) values ('[1, 2, 3]', '"test 9"');
insert into t2(key, value) values ('[1, 3]', '"test 10"');
insert into t2(key, value) values ('[1,2,4]', '"test 11"');
insert into t2(key, value) values ('[1, 2,4]', '"test 12"');
insert into t2(key, value) values ('[1,3,13]', '"test 13"');
insert into t2(key, value) values ('[1, 2, 15]', '"test 15"');

然后我尝试对这些行进行排序:

SELECT key FROM t2 order by key;

结果是:

[]
1
2
3
[1]
[2] <==
[3] <==
[1, 2]
[1, 3] <==
[1, 2, 3]
[1, 2, 4]
[1, 2, 4]
[1, 2, 15]
[1, 3, 13]

但是我需要的是

[]
1
2
3
[1]
[1, 2]
[1, 2, 3]
[1, 2, 4]
[1, 2, 4]
[1, 2, 15]
[1, 3] <==
[1, 3, 13]
[2] <==
[3] <==

有什么办法可以实现吗？

Answer 1

混合类型会有些痛苦，你可能并不意味着只有三个值 :) 但仍然是一个想法:

SELECT key FROM t2 order by (key->>0)::text::int,(key->>1)::int ,(key->>2)::int;

将给予:
“[1, 2, 3]”
“[1, 2, 4]”
“[1, 2, 4]”
“[1, 2, 15]”
“[1, 2]”
“[1, 3, 13]”
“[1, 3]”
“1”
“[1]”
“2”
“[2]”
“3”
“[3]”
“[]”