PostgreSQL get json key name after comparing values of keys



  • 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.



  • 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
    ;
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2