Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bG9Wy-0004hO-1o for pgsql-sql@arkaria.postgresql.org; Thu, 23 Jun 2016 18:39: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 1bG9Wx-00026r-Bx for pgsql-sql@arkaria.postgresql.org; Thu, 23 Jun 2016 18:39:11 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1bG9Ww-00026i-My for pgsql-sql@postgresql.org; Thu, 23 Jun 2016 18:39:10 +0000 Received: from mail-lf0-x244.google.com ([2a00:1450:4010:c07::244]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1bG9Wo-0003RK-Tt for pgsql-sql@postgresql.org; Thu, 23 Jun 2016 18:39:09 +0000 Received: by mail-lf0-x244.google.com with SMTP id l188so19287103lfe.0 for ; Thu, 23 Jun 2016 11:39:02 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=from:subject:message-id:date:to:mime-version; bh=o0BbgLpq07h/rKfNDHcl7xl9m5kaBz5Myj39s6o2qEk=; b=AzsFHWRUBkGWEdqSd/sCP2IYk1G16zRrHZp3fvzECb0Cj/TK5TKO/v9EjxR93IASJP 8JnkMV/LoL3BU7YZEHXZPXdNzkgbGXcVphlTOMV25RR5OfR83Q7cVZFgvZIzLFkxGX3r +nT09j76SdVoCbKwYMUhFUuHAy1gOb6xd+g5Vpm1+LMl5BUIB7ECYLLXS6QiPIMjvett TXzLaALEjV2lqdsvC2zT27PTUYwTSpRQZNhKgowotdXTu2moEeiF7x8pHpKRRH3xzQPl C/kYNxKRV0iNi3h1lc/+clWNgVFSd3fXMOz5XKza5Ex8dtj0gMO17S8vdjQQfHBD3ldB eF9A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:from:subject:message-id:date:to:mime-version; bh=o0BbgLpq07h/rKfNDHcl7xl9m5kaBz5Myj39s6o2qEk=; b=gnRvKuHMwouWe5sBnnWO18BuoU+XLtL19bJdLA39EfjgaI+wrIndh6lBcChHcyLauN TKlit+2V6fIJK4RGXE2SjNj72UODUqfaJRVD+FeyYrUkphP1HbCXbIX+HsZ7oP85liVk 8D1OL0GgBg1nEin17+mvaY/REqWjZh6L9t/wrAUzAm7yoBWWBZ0NB8psT5lW4mmvSo3d vYKAZTlgi5D6WxUfeX7od29c7ctC6TS6PRPWo+sVqscm6ZmxEQz1e6rixVhln9Vk78NK Jvl8pe0PkkxiOz4ZdJKzfd5Efkb7hTWKt4WTe+ZcDF/RHZskermh3eJlhJbAT8CYZP7F Hf7Q== X-Gm-Message-State: ALyK8tLejbK0Fss2KsjNBf7XdBlFA7KuBQvpT0ShiD55zcLA8JVCECaGVxMH37opb0n6lQ== X-Received: by 10.25.138.65 with SMTP id m62mr16141lfd.16.1466707140474; Thu, 23 Jun 2016 11:39:00 -0700 (PDT) Received: from [192.168.111.71] ([80.94.224.18]) by smtp.gmail.com with ESMTPSA id 134sm252095ljj.48.2016.06.23.11.38.58 for (version=TLS1 cipher=ECDHE-RSA-AES128-SHA bits=128/128); Thu, 23 Jun 2016 11:38:59 -0700 (PDT) From: Anton Ananich Content-Type: multipart/alternative; boundary="Apple-Mail=_12095235-8819-40E6-9CD3-775785E08408" Subject: Custom sort order with jsonb key Message-Id: <1BFCD444-70DD-4A32-B2AB-CEFCC2922419@gmail.com> Date: Thu, 23 Jun 2016 21:38:54 +0300 To: pgsql-sql@postgresql.org Mime-Version: 1.0 (Mac OS X Mail 8.2 \(2104\)) X-Mailer: Apple Mail (2.2104) X-Pg-Spam-Score: -2.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=_12095235-8819-40E6-9CD3-775785E08408 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=us-ascii 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 --Apple-Mail=_12095235-8819-40E6-9CD3-775785E08408 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=us-ascii
Dear = colleagues,

I have a table in = PostgreSQL with some data:

create table FOO (
    = key jsonb
);

insert into FOO(key) va= lues ('[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?

Regards,
Anthony Ananich
http://ananich.pro
= --Apple-Mail=_12095235-8819-40E6-9CD3-775785E08408--