0

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.

1
  • Your JSON is malformed. Are you using json or jsonb? "But this one compares all the keys inside the json." I'm confused: isn't that what you want? What is your expected result for the above JSON? Commented Mar 21, 2022 at 0:33

1 Answer 1

0

If you only want to look at two keys, add a WHERE clause to limit the rows returned by the json_each_text() function:

with cte as
(
   select *
        , genre.key as genre
        , row_number() over (partition by id order by value::int desc) as ord
    from base.maakunta
      cross join lateral json_each_text(base.maakunta.demographic->'he') genre
    where genre.key in ('he_miehet', 'he_naiset') --<< limit to the keys you want to check
)
select  genre, value,ord
from cte
where ord = 1
;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.