Custom sort order with jsonb key

agora inbox for pgsql-sql@postgresql.org  
help / color / mirror / Atom feed

Custom sort order with jsonb key
4+ messages / 4 participants
[nested] [flat]

* Custom sort order with jsonb key
@ 2016-06-23 18:38  Anton Ananich <anton.ananich@gmail.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Anton Ananich @ 2016-06-23 18:38 UTC (permalink / raw)
  To: 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?

Regards,
Anthony Ananich
http://ananich.pro <http://ananich.pro/;

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Custom sort order with jsonb key
@ 2016-06-23 19:03  David G. Johnston <david.g.johnston@gmail.com>
  parent: Anton Ananich <anton.ananich@gmail.com>
  0 siblings, 1 reply; 4+ messages in thread

From: David G. Johnston @ 2016-06-23 19:03 UTC (permalink / raw)
  To: Anton Ananich <anton.ananich@gmail.com>; +Cc: pgsql-sql

On Thu, Jun 23, 2016 at 2:38 PM, Anton Ananich <anton.ananich@gmail.com>
wrote:

> 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.

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Custom sort order with jsonb key
@ 2016-06-23 19:14  Steve Midgley <science@misuse.org>
  parent: David G. Johnston <david.g.johnston@gmail.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Steve Midgley @ 2016-06-23 19:14 UTC (permalink / raw)
  To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Anton Ananich <anton.ananich@gmail.com>; pgsql-sql

On Thu, Jun 23, 2016 at 12:03 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

> On Thu, Jun 23, 2016 at 2:38 PM, Anton Ananich <anton.ananich@gmail.com>
> wrote:
>
>> 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.
>
> 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):

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

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Custom sort order with jsonb key
@ 2016-06-23 19:40  Anthony Ananich <anton.ananich@gmail.com>
  parent: Steve Midgley <science@misuse.org>
  0 siblings, 0 replies; 4+ messages in thread

From: Anthony Ananich @ 2016-06-23 19:40 UTC (permalink / raw)
  To: pgsql-sql; +Cc: David G. Johnston <david.g.johnston@gmail.com>; Steve Midgley <science@misuse.org>


> 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 <mailto:david.g.johnston@gmail.com>> wrote:
> On Thu, Jun 23, 2016 at 2:38 PM, Anton Ananich <anton.ananich@gmail.com <mailto:anton.ananich@gmail.com>> wrote:
> 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.
> 
> 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):
> 
> 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
> 
> 

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
http://ananich.pro <http://ananich.pro/;

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

end of thread, other threads:[~2016-06-23 19:40 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-06-23 18:38 Custom sort order with jsonb key Anton Ananich <anton.ananich@gmail.com>
2016-06-23 19:03 ` David G. Johnston <david.g.johnston@gmail.com>
2016-06-23 19:14   ` Steve Midgley <science@misuse.org>
2016-06-23 19:40     ` Anthony Ananich <anton.ananich@gmail.com>

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox