Thread: Custom sort order with jsonb key
<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>
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.
On Thu, Jun 23, 2016 at 12:03 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
I spent a couple minutes goofing off on this question - this isn't exactly right and is UGLY, but maybe helps a bit with some ideas (the virtual table is needless, but my sql is rusty):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::intThere 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.
SELECT key, to_number(key#>>'{0}','9999') as order1, to_number(key#>>'{1}','9') as order2 FROM FOO
order by order1, order2
"[2014, 2, 4]";2014;2
"[2014, 2, 15]";2014;2
"[2014, 2, 4]";2014;2
"[2014, 2]";2014;2
"[2014, 2, 3]";2014;2
"[2014, 3]";2014;3
"[2014, 3, 13]";2014;3
"[2014]";2014;
"[2015]";2015;
"[2016]";2016;
2014 is coming after all the elements that have a value associated with 2014 - probably you could solve this with a coalesce, but I'm out of time messing with it. I hope it's helpful!
Steve
On Jun 23, 2016, at 22:14, Steve Midgley <science@misuse.org> wrote:On Thu, Jun 23, 2016 at 12:03 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:I spent a couple minutes goofing off on this question - this isn't exactly right and is UGLY, but maybe helps a bit with some ideas (the virtual table is needless, but my sql is rusty):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::intThere 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.SELECT key, to_number(key#>>'{0}','9999') as order1, to_number(key#>>'{1}','9') as order2 FROM FOOorder by order1, order2"[2014, 2, 4]";2014;2"[2014, 2, 15]";2014;2"[2014, 2, 4]";2014;2"[2014, 2]";2014;2"[2014, 2, 3]";2014;2"[2014, 3]";2014;3"[2014, 3, 13]";2014;3"[2014]";2014;"[2015]";2015;"[2016]";2016;2014 is coming after all the elements that have a value associated with 2014 - probably you could solve this with a coalesce, but I'm out of time messing with it. I hope it's helpful!Steve
In my case the array may be much longer and contain not just numbers, but also strings.
It may be easier to patch RDBMS sources. Where is this sort performed?
Regards,
Anthony Ananich