PostgreSQL 14中JSONB数组条件查询：筛选Apple的anz>3的记录

筛选Apple的anz值大于3的SQL语句 #

SELECT * FROM fruits
WHERE EXISTS (
    SELECT 1
    FROM jsonb_array_elements(items) AS elem
    WHERE elem->>'typ' = 'Apple'
      AND (elem->>'anz')::integer > 3
);

也可以使用jsonb_path_exists函数实现，语法更简洁：

SELECT * FROM fruits
WHERE jsonb_path_exists(items, '$.[] ? (@.typ == "Apple" && @.anz > 3)');

内容的提问来源于stack exchange，提问作者Rudi