PostgreSQL – Clever Way to Order JSON Item by Key

jsonorder-bypostgresqlsorting

I need to sort a json item in a record, so here are two examples:

First case

create table jsontable (
    jsonitem json
);

Next I insert a json item:

insert into jsontable(jsonitem) values ('{ "3" : "foo", "2" : "bar", "1" : "qux" }');

Then I query the values:

select t.jsonitem from jsontable t;
                 jsonitem                  
-------------------------------------------
 { "3" : "foo", "2" : "bar", "1" : "qux" }
(1 row)

Let's suppose the key is unique and is an integer > 0;

Question 1: ¿ How to sort the json item values by the key value, and get the following ?

{ "1" : "qux", "2" : "bar", "3" : "foo" }

Second case

This case use named keys and named values.

create table jsontable_arr(
   jsonitem json
   );

insert into jsontable_arr(jsonitem) values ('[  { "key" : "3" , "value": "foo"}, { "key" : "2" , "value": "bar"}, { "key" : "1" , "value": "qux"} ]');

select t.* from jsontable_arr t;
                                                jsonitem
--------------------------------------------------------------------------------------------------------
 [  { "key" : "3" , "value": "foo"}, { "key" : "2" , "value": "bar"}, { "key" : "1" , "value": "qux"} ]
(1 row)

Question 2: ¿ How to sort the json array item by the key value, and get the following?

[  { "key" : "1" , "value": "qux"}, { "key" : "2" , "value": "bar"}, {"key" : "3" , "value": "foo"} ]

Thanks for your suggestions.

Best Answer

Be aware that order of array items is a different issue than order of keys in JSON object. See:

For your first example (implying integer sort), simply casting to jsonb would do the job. It happens to sort keys the way you want by default:

SELECT jsonitem::jsonb FROM jsontable;

db<>fiddle here

Or use jsonb instead of json to begin with.

However, it's not strictly integer sort. Keys are strings. Shorter keys sort before longer keys, which works as desired with only digits. But not necessarily with anything else. Like negative numbers:

2, 3, -1

Then again, your solution does string sort according to the default collation, i.e. breaks integer sort for numbers not comprised of the same number of digits:

111, 22, 3

You didn't specify exactly. This would do integer sort properly:

select id, json_object_agg(key, value) AS jsonitem
from  (
   select id, key, value
   from   tbl
   ORDER  BY id, key::int  -- !!!
   ) tbl
group  by id;

Of course, the cast to integer raises an exception for any string that's not a legal integer representation.

Sorting in a subquery once as this is typically much faster than the per-item sort you get from an added ORDER BY in an aggregate function. This works, because, quoting the manual:

Alternatively, supplying the input values from a sorted subquery will usually work.

See:

Your second example is a different beast, trying to sort an array with JSON objects by the (single!?) nested key. You could reorder arrays like this:

SELECT j.id, arr.js_sorted
FROM   jsonarr j
CROSS  JOIN LATERAL (
   SELECT json_agg(value) AS js_sorted
   FROM (
      SELECT *
      FROM   json_array_elements(j.jsonitem)
      ORDER  BY (value->>'key')::int  -- true integer sort!
      ) sub
   ) arr;

It really depends on where your values come from, what they can be exactly, and how you want to sort exactly.

Related Question