Select jsonb key which has the highest value compare to other keys

Question

I have this jsonb column with name "demographics" . It looks like this

{

"genre":{"women":10,"men":5} 

}

I am trying to create new column which will pick the json key name of the one with highest value;

I tried this.

SELECT Greatest(demographics -> 'genre' ->> 'men',
       demographics -> 'genre' ->> 'women')
       AS greatest
FROM   demographics;  

This one will get "value" of the highest one but i want the key name and not the value. Basically i want that it returns in this case to the row "women" as it has higher value;

Answer 1

You can unpack the json object at genre to individual rows using jsonb_each and then get the greatest value using row_number or any number of methods:

with cte as 
(
   select *
        , genre.key as genre
        , row_number() over (partition by id order by value desc) as ord
     from demographics
    cross
     join lateral jsonb_each(demographics->'genre') genre
)
select id, genre, value 
  from cte
 where ord = 1
 

Here's a working demo on dbfiddle