Re: Custom sort order with jsonb key
| От | David G. Johnston |
|---|---|
| Тема | Re: Custom sort order with jsonb key |
| Дата | |
| Msg-id | CAKFQuwYAK58a36Yrh4NtSbj9i7b2uPjZjdJva+Qabmst+AEBFQ@mail.gmail.com обсуждение исходный текст |
| Ответ на | Custom sort order with jsonb key (Anton Ananich <anton.ananich@gmail.com>) |
| Ответы |
Re: Custom sort order with jsonb key
|
| Список | pgsql-sql |
Dear colleagues,I have a table in PostgreSQL with some data:
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?
Maybe try:
ORDER BY key->>1::int, key->>2::int, key->>3::int
There is no easy way, presently, to convert from a json array to a PostgreSQL array. If you do that I believe that those sort based upon the values and not lexically.
SELECT *
FROM ( VALUES (ARRAY[2014]::int[], ARRAY[2014,2]::int[], ARRAY[2015]::int[]) ) vals (v)
ORDER BY v;
David J.
В списке pgsql-sql по дате отправления: