json array how to sort descending and top 1



  • I have a jsonb field value like this:

    fname, amount
    -----  ------
    Merry, [ { "pid": 5, "sales": 100 }, { "pid": 1, "sales": 20 }, { "pid": 3, "sales": 125 }, { "pid": 2, "sales": 55 } ]
    Tom,   [ { "pid": 5, "sales": 20 },  { "pid": 1, "sales": 15 }, { "pid": 3, "sales": 110 }, { "pid": 2, "sales": 75 } ]
    Susan, [ { "pid": 5, "sales": 132 }, { "pid": 1, "sales": 120 }, { "pid": 3, "sales": 122 }, { "pid": 2, "sales": 155 } ]
    

    I want to show who has the highest sales amount, like below

    fname, amount, pid
    -----  ------  ---
    Susan, 155,    2
    Merry, 125,    3
    Tom,   110,    3
    

    I have tried below, but it doesn't show top 1

    Select a.*, item_object, position from table1 a,
    jsonb_array_elements(a.amount) with ordinality arr(item_object, position) Order by position desc
    

    how to archive this with postgresql ? thank you so much in advance

    Don



  • "Sales" is a peculiar name for a column obviously containing people's first names, not sales.

    You can use DISTINCT ON to get the top row per group.

    Select distinct on (sales) sales, item_object from table1 a,
    jsonb_array_elements(a.amount) arr(item_object) 
    Order by sales, (item_object->>'sales')::int desc;
    

    DISTINCT ON requires that you sort first by the 'group' column(s). To get the final output sorted by something else, you would need to wrap it in another query with a different ORDER BY, giving something like this (also taking the opportunity to parse out and relabel some columns):

    select * from (
       Select distinct on (sales) sales as fname, (item_object->'sales')::int true_sales, item_object->'pid' pid  
       from table1 a,
       jsonb_array_elements(a.amount) arr(item_object) 
       Order by sales, (item_object->>'sales')::int desc
    ) foo order by true_sales desc;
    

Log in to reply
 


Suggested Topics

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