0

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.

4
  • Why do you want your database to do unnatural things? Supposedly you need this sort order for display, so why not make the application layer do the ordering. – mustaccio Dec 26 '20 at 21:05
  • Good, that's one possible answer, let the application to that and not the database. – nesiax Dec 26 '20 at 21:17
  • @mustaccio: And why not let the RDBMS do it, which is typically much better at sorting? – Erwin Brandstetter Dec 27 '20 at 3:52
  • Is there any reason you are using the json datatype and not jsonb which is better for virtually all use cases? – Colin 't Hart Dec 27 '20 at 11:19
1

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.

0

The example above was the result of a json_object_agg function, according to the manual:

https://www.postgresql.org/docs/current/functions-aggregate.html

The aggregate functions array_agg, json_agg, jsonb_agg, json_object_agg, jsonb_object_agg, string_agg, and xmlagg, as well as similar user-defined aggregate functions, produce meaningfully different result values depending on the order of the input values.

However you can specify the order inside aggregates:

https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-AGGREGATES

aggregate_name (expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]

So, I finally found the solution while assembling the json object and is to specify the order inside the json_object_agg() function using an 'order by' like this:

select 
  json_object_agg(
      key, value order by key
  )  AS jsonitem,
from table key_value_tbl;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.