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.
json
datatype and notjsonb
which is better for virtually all use cases?