Custom sort order with jsonb key
| От | Anton Ananich |
|---|---|
| Тема | Custom sort order with jsonb key |
| Дата | |
| Msg-id | 1BFCD444-70DD-4A32-B2AB-CEFCC2922419@gmail.com обсуждение исходный текст |
| Ответы |
Re: Custom sort order with jsonb key
|
| Список | pgsql-sql |
Dear colleagues,
create table FOO (
key jsonb
);
insert into FOO(key) values ('[2014]');
insert into FOO(key) values ('[2015]');
insert into FOO(key) values ('[2016]');
insert into FOO(key) values ('[2014, 2]');
insert into FOO(key) values ('[2014, 2, 3]');
insert into FOO(key) values ('[2014, 3]');
insert into FOO(key) values ('[2014,2,4]');
insert into FOO(key) values ('[2014, 2,4]');
insert into FOO(key) values ('[2014,3,13]');
insert into FOO(key) values ('[2014, 2, 15]');
And I try to sort these rows like that:
SELECT key FROM FOO order by key;
The result is:
[2014]
[2015] <==
[2016] <==
[2014, 2]
[2014, 3] <==
[2014, 2, 3]
[2014, 2, 4]
[2014, 2, 4]
[2014, 2, 15]
[2014, 3, 13]
But what I need is
[2014]
[2014, 2]
[2014, 2, 3]
[2014, 2, 4]
[2014, 2, 4]
[2014, 2, 15]
[2014, 3] <==
[2014, 3, 13]
[2015] <==
[2016] <==
is there a way to achieve it?
Regards,
Anthony Ananich
В списке pgsql-sql по дате отправления: