Postgres Pro
Downloads
Home   >   mailing lists

Re: [SQL] Better way to sort a JSONB array? - Mailing list pgsql-sql

From Steve Midgley
Subject Re: [SQL] Better way to sort a JSONB array?
Date
Msg-id CAJexoSJcYLH4LJNUZ6RUzHr4P6+qpP+YNdY3QgdUcHix3U_wzg@mail.gmail.com
Whole thread Raw
In response to [SQL] Better way to sort a JSONB array?  (Michael Moore <michaeljmoore@gmail.com>)
Responses Re: [SQL] Better way to sort a JSONB array?  (Michael Moore <michaeljmoore@gmail.com>)
List pgsql-sql
Tree view
On Mon, Aug 7, 2017 at 1:13 PM, Michael Moore <michaeljmoore@gmail.com> wrote:
This works, but surely there is a better way to do it:

 select jsonb_agg(row_to_json(alias)) from 
       (Select * from jsonb_populate_recordset(null::tx_portal, json_table2) order by portal_name) alias 
                                                           into json_table2;

It sorts the json_table2 array in "portal_name" order. 

I'm confused why you can't just pull portal_name from the json structure using "->"? For example, assuming your json structure that looks like:

{"portal_name": "some data.."}

And a table that looks like:

| id | json_field |

Wouldn't this sql work:

select * from json_table
order by json_field->'portal_name'

Steve

pgsql-sql by date:

Previous
From: Michael Moore
Date:
Subject: [SQL] Better way to sort a JSONB array?
Next
From: Michael Moore
Date:
Subject: Re: [SQL] Better way to sort a JSONB array?