Postgres：从值 >= x 的对象数组中选择所有行，以及没有值大于 x 的所有行

Question

我有一个表，看起来像这样:

sentence  data  
good     [{"pred": "yes", 'prob': 0.6}, {"pred": "maybe", "prob": 0.4}, {"pred": "another", "prob": 0.7}]
bad      [{"pred": "unexpected", "prob": 0.4}, {"pred": "uncool", "prob": 0.3}]

我想输出所有的preds对于有prob >= 0.5的sentence。然而，如果一个句子没有probs大于0.5，那么我也想把它包括在结果中。

例如，对于这个数据，结果应该是: 结果:

sentence | preds 
-----------+-------
good      | ['yes', 'another']
bad       | null    
(2 rows)

我这样做了，这适用于第一种情况(选择preds与prob >= 0.5)。但是，我无法选择没有probs大于0.5的句子

SELECT sentence, jsonb_agg(data->'pred') AS preds
FROM table
CROSS JOIN jsonb_array_elements(table.data) AS data
WHERE data->>'prob' >= '0.5'
GROUP BY sentence

Answer 1

如果您使用的是Postgres 12或更高版本，您可以使用JSON路径查询:

select sentence, 
jsonb_path_query_array(data, '$[*] ? (@.prob >= 0.5).pred') as preds
from the_table;

这将返回一个空数组[]，如果没有任何项符合条件。

---

对于较早的版本，我会使用:

select t.sentence, 
(select jsonb_agg(e.item -> 'pred')
from jsonb_array_elements(t.data) as e(item)
where (e.item ->> 'prob')::float >= 0.5) as preds
from the_table t;

对于没有元素匹配的

返回null

Answer 2

试试left join lateral:

# with invars (sentence, data) as (
values
('good', '[{"pred": "yes", "prob": 0.6}, {"pred": "maybe", "prob": 0.4}, {"pred": "another", "prob": 0.7}]'::jsonb),
('bad', '[{"pred": "unexpected", "prob": 0.4}, {"pred": "uncool", "prob": 0.3}]')
)
select sentence, jsonb_agg(d.data) as preds
from invars
left join lateral jsonb_array_elements(data) as d(data)
on (d.data->>'prob')::numeric >= .5
group by sentence;
┌──────────┬──────────────────────────────────────────────────────────────────┐
│ sentence │                            jsonb_agg                             │
├──────────┼──────────────────────────────────────────────────────────────────┤
│ bad      │ [null]                                                           │
│ good     │ [{"pred": "yes", "prob": 0.6}, {"pred": "another", "prob": 0.7}] │
└──────────┴──────────────────────────────────────────────────────────────────┘
(2 rows)