postgresql json data return key with max value

Question

I have json data stored something like this:

{"values":{"a":0.65, "b":0.35}}

I want to return the key which has the largest value in "values"

So in above case, I want to return:

since it has the largest value

Some of the rows have no values in the data which will have:

{}

as a default

I need to handle those entries with {} and return Null as well.

Tometzky · Accepted Answer · 2014-12-10 19:02:41Z

4

With sample data:

create temporary table tmp as
  select '{"values":{"a":0.65, "b":0.35}}'::json as j;
insert into tmp values ('{}'::json);

You could do something like:

select (
  select key from json_each_text(j::json->'values')
  order by value::float desc limit 1
) as k
from tmp;

answered Dec 10, 2014 at 19:02

Tometzky

24.2k5 gold badges64 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1