PostgreSQL get json key name after comparing values of keys
0
votes
1
answer
2038
views
I have table which has a column of type json with name "demographic". I want to compare keys(predefined by me) by their values and the result of that should be the name of the key and not the value which later i will use to save to another column's rows.
The json columns that i have and want to compare values looks like this:
{
"he":{
"he_vakiy":29384,
"he_naiset":14803,
"he_miehet":14581,
"he_kika":1485,
"he_0_2":850,
"he_3_6"
}
}
I want to compare f.ex "he_naiset" and "he_miehet" and the one that has highest value should return the name of the "key" ;
Ive tried something like this:
select greatest(demographics->'he'->>'he_miehet',demographics->'he'->>'he_naiset') as greatest
from demographics;
Which will compare the values and return the highest values. But I don't know how I can get the name of the key.
I also tried something like this from:
with cte as
(
select *
, genre.key as genre
, row_number() over (partition by id order by value desc) as ord
from base.maakunta
cross
join lateral json_each_text(base.maakunta.demographic->'he') genre
)
select genre, value,ord
from cte
where ord = 1
;
But this one compares all the keys inside the JSON.
Asked by eko
(101 rep)
Mar 20, 2022, 02:48 PM
Last activity: Aug 5, 2025, 05:03 PM
Last activity: Aug 5, 2025, 05:03 PM