Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bGAUy-0007ts-NQ for pgsql-sql@arkaria.postgresql.org; Thu, 23 Jun 2016 19:41:12 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1bGAUx-0004jT-NM for pgsql-sql@arkaria.postgresql.org; Thu, 23 Jun 2016 19:41:11 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1bGAUw-0004jK-2X for pgsql-sql@postgresql.org; Thu, 23 Jun 2016 19:41:10 +0000 Received: from mail-wm0-x231.google.com ([2a00:1450:400c:c09::231]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1bGAUr-0004iS-9Q for pgsql-sql@postgresql.org; Thu, 23 Jun 2016 19:41:09 +0000 Received: by mail-wm0-x231.google.com with SMTP id a66so63788309wme.0 for ; Thu, 23 Jun 2016 12:41:05 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:subject:from:in-reply-to:date:cc:message-id:references :to; bh=EcRuhu1A3FDzE3hx6NnWpcGcKtoBGVNRR64mIQiYWxI=; b=opleaFLVbVstBcYxZsJxlnL3gX9WiIJafOHROxoUAFnkEI+0mf8Kb8dQ9nECxOKVcn YgSmqwtqDoKhYgYp31mrZdHJbFje4w6zXpkxezIOvJEVDebp0TCdMga+OWYXsHejIaFd uo1+T9FeAykZBlw3Ay4eMsNj9Ctg+FPtyZ36ZIgsizC8NERtYaMfSBM0e8sCcHjpx1nf 5O/rC0GYZBSL+t7SKsOrv2FlRTmQTEroinoYUjcHFlwIhzqu3BbSx7afk3xSmeN5cLCj 8QIBlk09dFXTsnNgo4AKUVeNCCvYvDzPs4yB65cA98NPfxW4HyV3rxEaMhqfq5y0Ekjm B9Vw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:mime-version:subject:from:in-reply-to:date:cc :message-id:references:to; bh=EcRuhu1A3FDzE3hx6NnWpcGcKtoBGVNRR64mIQiYWxI=; b=C0mo2lXMI5G0NgbxWhpjNgDCrikR5ETinmEh7JB506vOdnqP5CsbSCPyss9Y1nrwYx QX/ekTVdaM93kh4giemW2OuW4Zzon0BsBOEcPxK4PFP1yMDwyFo81dMRpwE9Tp/muWhd 12K0pYoRWER6Jhwf1dJgiTT8ig+7SXgyt1S/VYSXwMEpDPk35gxj/8YLZV+3WpalET9n Cjhj5yegdgywd6g2U8IiUjTv8yKmQmjox0ARoy1C58l5j6ywJcs0+myfnVrnVeeNCGdF IX8rkR6YmvsJrdi4WDBxtQvcfJ7gYTT2cLiJAyBK2Itp+T+kZsI3YXiq74R+Hps6zRn/ R2jw== X-Gm-Message-State: ALyK8tLS0+jDToNwByYiHjopYc9Ql5kQhFrvTuLCBvirFWCmgKNW2RgkzIkXMll7Q88GOQ== X-Received: by 10.194.236.195 with SMTP id uw3mr186185wjc.149.1466710863513; Thu, 23 Jun 2016 12:41:03 -0700 (PDT) Received: from [192.168.111.71] ([80.94.224.18]) by smtp.gmail.com with ESMTPSA id q63sm5311966wma.0.2016.06.23.12.41.00 (version=TLS1 cipher=ECDHE-RSA-AES128-SHA bits=128/128); Thu, 23 Jun 2016 12:41:00 -0700 (PDT) Content-Type: multipart/alternative; boundary="Apple-Mail=_5FC2B5B4-F014-4A96-AC2E-9E5167482DA7" Mime-Version: 1.0 (Mac OS X Mail 8.2 \(2104\)) Subject: Re: Custom sort order with jsonb key From: Anthony Ananich In-Reply-To: Date: Thu, 23 Jun 2016 22:40:55 +0300 Cc: "David G. Johnston" , Steve Midgley Message-Id: References: <1BFCD444-70DD-4A32-B2AB-CEFCC2922419@gmail.com> To: "pgsql-sql@postgresql.org" X-Mailer: Apple Mail (2.2104) X-Pg-Spam-Score: -1.7 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org --Apple-Mail=_5FC2B5B4-F014-4A96-AC2E-9E5167482DA7 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On Jun 23, 2016, at 22:14, Steve Midgley wrote: >=20 >=20 >=20 > On Thu, Jun 23, 2016 at 12:03 PM, David G. Johnston = > wrote: > On Thu, Jun 23, 2016 at 2:38 PM, Anton Ananich = > wrote: > Dear colleagues, >=20 > I have a table in PostgreSQL with some data: >=20 > create table FOO ( > key jsonb > ); >=20 > 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]'); >=20 > And I try to sort these rows like that: >=20 > SELECT key FROM FOO order by key; >=20 > The result is: >=20 > [2014] > [2015] <=3D=3D > [2016] <=3D=3D > [2014, 2] > [2014, 3] <=3D=3D > [2014, 2, 3] > [2014, 2, 4] > [2014, 2, 4] > [2014, 2, 15] > [2014, 3, 13] >=20 > But what I need is >=20 > [2014] > [2014, 2] > [2014, 2, 3] > [2014, 2, 4] > [2014, 2, 4] > [2014, 2, 15] > [2014, 3] <=3D=3D > [2014, 3, 13] > [2015] <=3D=3D > [2016] <=3D=3D >=20 > is there a way to achieve it? >=20 > =E2=80=8BMaybe try: >=20 > ORDER BY key->>1::int=E2=80=8B, key->>2::int, key->>3::int >=20 > 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. >=20 > SELECT *=20 > FROM ( VALUES (ARRAY[2014]::int[], ARRAY[2014,2]::int[], = ARRAY[2015]::int[]) ) vals (v)=20 > ORDER BY v; >=20 > David J. >=20 > 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): >=20 > SELECT key, to_number(key#>>'{0}','9999') as order1, = to_number(key#>>'{1}','9') as order2 FROM FOO > order by order1, order2 >=20 > "[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; >=20 > 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! >=20 > Steve >=20 >=20 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 --Apple-Mail=_5FC2B5B4-F014-4A96-AC2E-9E5167482DA7 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8
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:
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] <=3D=3D
[2016] <=3D=3D
[2014, 2]
[2014, 3] <=3D=3D
[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] <=3D=3D
[2014, 3, 13]
[2015] <=3D=3D[2016] <=3D=3D

is = there a way to achieve it?

=E2=80=8BMaybe try:

ORDER BY key->>1::int=E2=80=8B, 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


= --Apple-Mail=_5FC2B5B4-F014-4A96-AC2E-9E5167482DA7--