[POSTGRESQL] - Get key name from json column after comparing keys values?

[erkanisuf]

I have table which has a column of type json with name "demographic". I want to compare keys by their values predefined by me and the result of that should be the name of the key and not the value which i will save to another column's rows.

The json columns looks like a 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 higher 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 higest values. But i dont 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 .