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 |
<div class="">Dear colleagues,</div><div class=""><br class="" /></div>I have a table in PostgreSQL with some data:<br class=""/><br class="" />create table FOO (<br class="" /> key jsonb<br class="" />);<br class="" /><br class="Apple-interchange-newline"/>insert into FOO(key) values ('[2014]');<br class="" />insert into FOO(key) values ('[2015]');<brclass="" />insert into FOO(key) values ('[2016]');<br class="" />insert into FOO(key) values ('[2014,2]');<br class="" />insert into FOO(key) values ('[2014, 2, 3]');<br class="" />insert into FOO(key) values ('[2014,3]');<br class="" />insert into FOO(key) values ('[2014,2,4]');<br class="" />insert into FOO(key) values ('[2014,2,4]');<br class="" />insert into FOO(key) values ('[2014,3,13]');<br class="" />insert into FOO(key) values ('[2014,2, 15]');<div class=""><br class="" />And I try to sort these rows like that:<br class=""/><br class="" />SELECT key FROM FOO order by key;</div><div class=""><br class="" />The result is:<br class="" /><brclass="" />[2014]<br class="" />[2015] <==<br class="" />[2016] <==<br class="" />[2014, 2]<br class="" />[2014, 3] <==<brclass="" />[2014, 2, 3]<br class="" />[2014, 2, 4]<br class="" />[2014, 2, 4]<br class="" />[2014, 2, 15]<brclass="" />[2014, 3, 13]</div><div class=""><br class="" />But what I need is<br class="" /><br class=""/>[2014]<br class="" />[2014, 2]<br class="" />[2014, 2, 3]<br class="" />[2014, 2, 4]<br class="" />[2014, 2, 4]<brclass="" />[2014, 2, 15]<br class="" />[2014, 3] <==<br class="" />[2014, 3, 13]<br class="" />[2015] <==<brclass="" />[2016] <==</div><div class=""><br class="" />is there a way to achieve it?</div><div class=""><brclass="" /></div><div class="">Regards,</div><div class="">Anthony Ananich</div><div class=""><a class="" href="http://ananich.pro">http://ananich.pro</a></div>
В списке pgsql-sql по дате отправления: