Sample Header Ad - 728x90

Clever way to order a json item by key in postgresql (two examples)

2 votes
2 answers
21736 views
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.
Asked by nesiax (43 rep)
Dec 26, 2020, 05:48 PM
Last activity: Jun 12, 2024, 02:08 PM