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
CodePudding user response:
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;
CodePudding user response:
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
Let me know if this works for you.