Find object having the highest value in JSONB array of objects in Postgresql

user_3644961 0
Viewed 111

Assuming we have a table where column data stores an array of objects.

┌────┬───────────────────────────────────────────────────────────────────────┐
│ id │                               data                                    │
├────┼───────────────────────────────────────────────────────────────────────┤
│  1 │ [{"m2": 40.0, "sector": "Office"}, {"m2": 65.0, "room": "Hospital"}]  │
│  2 │ [{"m2": 25.0, "sector": "Cafe"}, {"m2": 120.0, "room": "Office"}]     │
│  3 │ []                                                                    │
└────┴───────────────────────────────────────────────────────────────────────┘

I would like to find only these objects, which have the m2 value the highest.

┌────┬─────────────────────────────────────────┐
│ id │                 data                    │
├────┼─────────────────────────────────────────┤
│  1 │ {"m2": 65.0, "room": "Hospital"}        │
│  2 │ {"m2": 120.0, "room": "Office"}         │
│  3 │ null                                    │
└────┴─────────────────────────────────────────┘

PostgreSQL 13.4

jsonb greatest-n-per-group postgresql sql
2 Answers

You may try the following which uses json_array_elements to expand the array before using row_number to determine the object with the highest m2 value.

select 
    "id", 
    (
       SELECT 
           jr 
       FROM (
           SELECT
               jr,
               ROW_NUMBER() OVER (
                   ORDER BY jr->'m2' DESC
               ) as rn
           FROM
               jsonb_array_elements("data") as jr
       ) t1
       WHERE rn=1
    ) as "data"
from 
    my_table

or simply ordering by m2 and limiting your result to 1 eg

select 
    "id", 
    (
       SELECT 
           jr 
       FROM 
           jsonb_array_elements("data") as jr
       ORDER BY 
           jr->'m2' DESC
       LIMIT 1
    ) as "data"
from 
    my_table

View working demo db fiddle

Let me know if this works for you.

user_43071360

You can use a subquery:

with m_d(id, a) as (
    select t1.id, (select json_agg(v.value) from jsonb_array_elements(t1.data::jsonb) v 
    where (v.value -> 'm2')::text::float = (select max((v2.value -> 'm2')::text::float) 
           from jsonb_array_elements(t1.data::jsonb) v2)) from t t1
)
select id, case when json_array_length(a) = 0 then null else a ->> 0 end from m_d;
user_73267380
Related