Sample Header Ad - 728x90

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